Query Performance tuning for cursor

Question

Query Performance tuning for cursor

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Junglee_George (11/4/2015)
Hey, This is how I tuned my query to avoid cursor in my code. Hope it helps. ...
If any suggestions are there, all are welcome.
CREATE TABLE #Capacity (CurDay DATE, WeekDy NVARCHAR(10), Baseline FLOAT, NegativeGap FLOAT, PositiveGap FLOAT)
DECLARE @StartDate DATE
DECLARE @EndDate DATE
SET @StartDate = CONVERT(DATE, @FromDate)
SET @EndDate = CONVERT(DATE, @ToDate)
DECLARE @assignedFromDate DATE
DECLARE @assignedUntilDate DATE
DECLARE @workloadPercentage FLOAT
SELECT assignedFromDate
,assignedUntilDate
,lv.workloadPercentage
INTO #temp1
FROM Local_SubUnitsOperators AS LV
INNER JOIN Operators AS OP ON (
OP.operatorIncId = LV.operatorIncId
AND OP.operatorSqlId = LV.operatorSqlId
AND OP.isDeleted = 0x0
AND LV.isDeleted = 0x0
AND CHARINDEX(OP.operatorCode, @operator) > 0
)
INNER JOIN SubUnits AS S ON (
LV.subUnitIncId = S.subUnitIncId
AND LV.subUnitSqlId = S.subUnitSqlId
AND S.isDeleted = 0x0
AND S.subUnitCode = @subUnitCode
)
WHERE assignedFromDate <= @ToDate
DECLARE @index INT
DECLARE @RecordCnt INT
SELECT @index = 1
SELECT @RecordCnt = COUNT(*) FROM #temp1
WHILE (@Index <= @RecordCnt)
BEGIN
DECLARE @tempassignedFromDate DATE
DECLARE @tempassignedUntilDate DATE
----SET @tempassignedFromDate = @assignedFromDate
IF (@assignedFromDate < @FromDate)
BEGIN
SET @tempassignedFromDate = @FromDate
END
ELSE
BEGIN
SET @tempassignedFromDate = @assignedFromDate
END
IF (@assignedUntilDate > @ToDate)
BEGIN
SET @tempassignedUntilDate = @ToDate
END
ELSE
BEGIN
SET @tempassignedUntilDate = @assignedUntilDate
END
WHILE (@tempassignedFromDate <= @tempassignedUntilDate)
BEGIN
INSERT INTO #Capacity
SELECT@tempassignedFromDate,
DATENAME(WEEKDAY, @tempassignedFromDate),
CASE WHEN DATENAME(WEEKDAY, @tempassignedFromDate) IN ('Saturday','Sunday') THEN 0
ELSE @workloadPercentage END AS workloadPercentage,
(SELECT ISNULL(CASE WHEN DATENAME(WEEKDAY, @tempassignedFromDate) IN ('Saturday' ,'Sunday') THEN 0
ELSE SUM(CONVERT(FLOAT, LV.workloadPercentage) * 0.5 * ROUND(DATEDIFF(HOUR, CASE WHEN ASE.activitySessionStartOnUTC > @tempassignedFromDate THEN ASE.activitySessionStartOnUTC
ELSE @tempassignedFromDate
END,
CASE WHEN ASE.activitySessionEndOnUTC < DATEADD(DAY, 1, @tempassignedFromDate) THEN ASE.activitySessionEndOnUTC
ELSE DATEADD(DAY, 1, @tempassignedFromDate) END) / 12.0, 0))
END, 0) --KEEP floating value otherwise will return an integer
FROM Local_SubUnitsOperators AS LV
INNER JOIN Operators AS O
INNER JOIN ActivitiesSessions AS ASE
INNER JOIN TypesOfActivitiesSessions AS TASE ON (
ASE.typeOfActivitySessionIncId = TASE.typeOfActivitySessionIncId
AND ASE.typeOfActivitySessionSqlId = TASE.typeOfActivitySessionSqlId
AND TASE.typeOfActivitySessionCode IN ('HO','IL')
) ON (
O.operatorIncId = ASE.doneByIncId
AND O.operatorSqlId = ASE.doneBySqlId
AND ASE.activitySessionEndOnUTC >= @tempassignedFromDate
AND ASE.activitySessionStartOnUTC <= DATEADD(DAY, 1, @tempassignedFromDate)
) ON (
LV.operatorIncId = O.operatorIncId
AND LV.operatorSqlId = O.operatorSqlId
AND LV.isDeleted = 0x0
AND CHARINDEX(O.operatorCode, @operator) > 0
) INNER JOIN SubUnits AS S ON (
LV.subUnitIncId = S.subUnitIncId
AND LV.subUnitSqlId = S.subUnitSqlId
AND S.subUnitCode = @subUnitCode
)),
(SELECT ISNULL(CASE WHEN DATENAME(weekday, @tempassignedFromDate) IN ('Saturday','Sunday') THEN SUM(CONVERT(FLOAT, LV.workloadPercentage) * 0.5 * ROUND(DATEDIFF(HOUR,
CASE WHEN ASE.activitySessionStartOnUTC > @tempassignedFromDate THEN ASE.activitySessionStartOnUTC
ELSE @tempassignedFromDate END,
CASE WHEN ASE.activitySessionEndOnUTC < DATEADD(DAY, 1, @tempassignedFromDate) THEN ASE.activitySessionEndOnUTC
ELSE DATEADD(DAY, 1, @tempassignedFromDate)
END) / 12.0, 0)) ELSE 0 END, 0) --KEEP floating value otherwise will return an integer.
FROM Local_SubUnitsOperators AS LV
INNER JOIN Operators AS O
INNER JOIN ActivitiesSessions AS ASE
INNER JOIN TypesOfActivitiesSessions AS TASE ON (
ASE.typeOfActivitySessionIncId = TASE.typeOfActivitySessionIncId
AND ASE.typeOfActivitySessionSqlId = TASE.typeOfActivitySessionSqlId
AND TASE.typeOfActivitySessionCode = 'WO'
) ON (
O.operatorIncId = ASE.doneByIncId
AND O.operatorSqlId = ASE.doneBySqlId
AND ASE.activitySessionEndOnUTC >= @tempassignedFromDate
AND ASE.activitySessionStartOnUTC <= DATEADD(DAY, 1, @assignedFromDate)
) ON (
LV.operatorIncId = O.operatorIncId
AND LV.operatorSqlId = O.operatorSqlId
AND LV.isDeleted = 0x0
AND CHARINDEX(O.operatorCode, @operator) > 0
) INNER JOIN SubUnits AS S ON (
LV.subUnitIncId = S.subUnitIncId
AND LV.subUnitSqlId = S.subUnitSqlId
AND S.subUnitCode = @subUnitCode
))
SET @tempassignedFromDate = DATEADD(DAY, 1, @tempassignedFromDate)
END
SET @index = @index + 1
END
END

The cursor is not the evil part of your code. The evil part is looping. All you have done is turn a cursor into a different kind of loop. You still have nested loops going on here which is just plain awful from a performance perspective. What you have done is an exercise in futility. If you want this to be fast you MUST get rid of the looping. If you want help with that you must provide us details to work with. You aren't new around here so you know what needs to be posted.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Junglee_George (11/4/2015)
Hey, This is how I tuned my query to avoid cursor in my code. Hope it helps. ...
If any suggestions are there, all are welcome.

My suggestion is for you to review the suggestions previously made which weren't addressed at all.

I suggest you to check this article as well, since you believe that a while loop is an improvement. http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2