July 8, 2015 at 2:46 am
Hello
I have a cursor part in my query which needs to be performance tuned. Please suggest any changes in SQL code that can improve the performance of the query
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 @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT assignedFromDate
,assignedUntilDate
,workloadPercentage
FROM #temp1
OPEN @MyCursor
FETCH NEXT
FROM @MyCursor
INTO @assignedFromDate
,@assignedUntilDate
,@workloadPercentage
WHILE @@FETCH_STATUS = 0
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
FETCH NEXT FROM @MyCursor
INTO @assignedFromDate,@assignedUntilDate,@workloadPercentage
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
July 8, 2015 at 6:00 am
Doing an operation like this with a large query like that within a cursor is extremely problematic. Instead of a cursor, I'd suggest modifying or loading your temp table such that you can just use it as a join operation, rather than setting those variables and then calling that big query over and over.
All the functions on the WHERE clauses within that procedure, stuff like this:
CHARINDEX(O.operatorCode, @operator) > 0
Are going to cause scans. You'll need to eliminate them. All of them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2015 at 8:54 am
Instead of removing cursor, please help to me to modify the existing code within.
July 8, 2015 at 9:20 am
Junglee_George (7/8/2015)
Instead of removing cursor, please help to me to modify the existing code within.
I think you are missing the point. The reason the performance of this is awful is because it is using a cursor. What you are asking similar to "Here is my Yugo, can you make it fast enough to race formula one?". Simply put, you can't make this faster AND keep the cursor. If you want it fast you have to get rid of the cursor.
_______________________________________________________________
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/
July 8, 2015 at 9:43 am
Sean Lange (7/8/2015)
Junglee_George (7/8/2015)
Instead of removing cursor, please help to me to modify the existing code within.I think you are missing the point. The reason the performance of this is awful is because it is using a cursor. What you are asking similar to "Here is my Yugo, can you make it fast enough to race formula one?". Simply put, you can't make this faster AND keep the cursor. If you want it fast you have to get rid of the cursor.
Agree with both Sean and Grant here. To improve the performance, you need to get rid of the cursor. The good news is that this one looks pretty easy to do... once you get rid of the row-by-row mentality and start thinking in sets.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2015 at 1:15 am
Ok..I agree with Wayne. Please help me in get rid of the cursor by alternate code.
I am new to this set based approach, removing the cursor.
July 9, 2015 at 2:47 am
Junglee_George (7/8/2015)
Instead of removing cursor, please help to me to modify the existing code within.
I did. Those functions on columns are going to kill your performance. You must remove them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2015 at 6:42 am
Would be grateful if any of the members can post a sample alternate way for my cursor part. This will throw light how to proceed on this.
Also, suggest some way so that I can get rid of CHARINDEX functions used in WHERE clause.
July 9, 2015 at 6:50 am
Its going to be tricky to help refactor your code without the code to create the tables \ sample data that the query is using. Can you post up that as well?
MCITP SQL 2005, MCSA SQL 2012
July 9, 2015 at 7:08 am
It looks as though all your cursor is doing is adding 1 to a date each iteration. You can use a tally for this type of thing which is easier to write and maintain in addition to being a lot faster. You can read about the technique here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
If after reading that article you still need some help please follow the advice suggested previously about posting ddl and sample data.
_______________________________________________________________
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/
July 9, 2015 at 7:52 am
Junglee_George (7/9/2015)
Ok..I agree with Wayne. Please help me in get rid of the cursor by alternate code.I am new to this set based approach, removing the cursor.
Well, you're going to need to help us so that we can help you.
Start off by reading the first link in my signature. That will tell you what you should post here to help us.
Specifically, we're going to need:
CREATE TABLE statements for the Local_SubUnitsOperators, Operators, and SubUnits tables. Ensure that this includes primary key / unique constraints.
CREATE INDEX statements for any other existing indexes on these tables.
INSERT statements for all of these tables that will put representative sample data into those tables. Ensure that this data will show us what you are working with.
And, just as important as these two, what you expect the results to look like.
Finally, make sure that you read that link, and follow the suggestions in it. Remember, we are volunteers here, so the more work you do to make it easier for us, the faster someone will be likely to help you with your issue.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2015 at 8:24 am
Isn't the CHARINDEX just checking the existence of the value in a column? Can't you use equals? If not, can you use LIKE? If you really are searching through an entire column for a value, I think you may need to restructure your table, possibly using a calculated column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 10, 2015 at 7:46 am
That's a horrendous complicated query! Personally I think it needs a complete rewrite.
But if you don't have time and want to improve the performance the first thing to check is the indexes on your tables. To do this you need to identify all the queries in the SQL and check that appropriate indexes are present on the tables for the inner joins.
July 10, 2015 at 9:19 am
This procedure can be changed into set-based code. I just don't want to risk posting an incorrect solution and getting blamed for that as I don't have any sample data to test on or more details on what the code is meant to do.
For a minor change that might help a bit, you could wrap everything inside a single transaction. That will also help you to correct errors that you might get if the procedure fails without completing.
November 4, 2015 at 7:29 am
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
END
END
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply