October 24, 2013 at 9:48 am
Hey Guys,
A while ago, I learnt that replacing cursors with while loops helps during performance optimization. I have done this successfully in most cases during my career but I found a case where replacing the cursor with a while loop did not change much.
I did some more research and found out that we should try and stay away from while loops as much as possible. That begs the question, how do you replace a while loop (without going back to cursors).
I have gone through the liberty of creating some temp tables and populating them with some data. I also wrote a simple while loop. How can you replace this with a more optimal query. I just read about using cross apply the other day and I am not sure if that would help here. I wouldn't even know how to begin using it.
Any code optimization refactoring would help. The current while loop has about CPU 31, 1084 reads, 1 write and 38 milli seconds duration in the sql profiler. I will test any code and give you the profiler results when done.
Thanks again.
--DDL SET UP BEGINS
CREATE TABLE #PAO
(PAOID int, PQID int, PAOV varchar(50), IsReal bit)
INSERT INTO #PAO (PAOID, PQID, PAOV, IsReal)
SELECT 1,1, 'Cristiano Ronaldo is the best', 1 UNION ALL
SELECT 2,1, 'Messi is the best', 1 UNION ALL
SELECT 3,1, 'Maradona is the best', 1 UNION ALL
SELECT 4,1,'Pele is the best', 1 UNION ALL
SELECT 5,1,'Puskas is the best', 1 UNION ALL
SELECT 6,1,'Ronaldo Luiz is the best', 1 UNION ALL
SELECT 7,2,'Eusebio is the best', 1 UNION ALL
SELECT 8,2,'Ibrahimovic is the best', 1 UNION ALL
SELECT 9,2,'Nesta is the best', 1 UNION ALL
SELECT 10,3,'Maldini is the best', 1
CREATE TABLE #PAOT
(PAOTID int, PAOID int, ID int, IsReal bit)
INSERT INTO #PAOT (PAOTID, PAOID, ID, IsReal )
SELECT 1,1,1,1 UNION ALL
SELECT 3,1,2,1 UNION ALL
SELECT 5,1,3,1 UNION ALL
SELECT 7,1,3,1 UNION ALL
SELECT 8,1,4,1 UNION ALL
SELECT 10,1,5,1 UNION ALL
SELECT 12,1,6,1 UNION ALL
SELECT 13,7,6,1 UNION ALL
SELECT 14,8,6,1 UNION ALL
SELECT 16,1,7,1
CREATE TABLE #EP
(EPID int, EID int, PQID int, PAOID int, PAOV varchar(50))
INSERT INTO #EP (EPID, EID, PQID, PAOID, PAOV)
SELECT 1,47,1,1,'1.1' UNION ALL
SELECT 2,47,2,9,'2.3' UNION ALL
SELECT 3,47,3,11,'2a.2' UNION ALL
SELECT 4,47,4,12,'2b.1' UNION ALL
SELECT 5,47,5,58,'1' UNION ALL
SELECT 6,47,5,15,'116' UNION ALL
SELECT 7,47,6,287,'218' UNION ALL
SELECT 8,47,7,439,'218' UNION ALL
SELECT 9,47,8,449,'6.1.2' UNION ALL
SELECT 10,47,9,451,'6.2.2'
Declare @Qid int
Declare @Aid int
Declare @Loop int
Declare @Temp1 TABLE (ID INT NOT NULL)
INSERT INTO @Temp1 (ID)
SELECT DISTINCT ID
FROM #PAO
INNER JOIN #PAOT ON #PAO.PAOID = #PAOT.PAOID
INNER JOIN #EP ON (
(#PAO.PAOID = #EP.PAOID
AND #EP.PQID = 1)
)
WHERE (#PAO.IsReal = 1)
AND (#PAO.PQID = 1)
AND (#EP.EID = 47)
DECLARE @Temp2 TABLE (FID INT IDENTITY, QID INT NOT NULL, AID INT NOT NULL)
INSERT INTO @Temp2
SELECT DISTINCT #EP.PQID
,#EP.PAOID
FROM #PAO
INNER JOIN #EP
ON (
(#PAO.PQID = #EP.PQID)
AND (#PAO.PAOID = #EP.PAOID)
)
WHERE (#PAO.IsReal = 1)
AND (#PAO.PQID <> 1)
AND (#EP.EID = 47)
--DDL SET UP ENDS
--WHILE LOOP BEGINS
WHILE (@Loop > 0)
BEGIN
SELECT TOP 1 @QId = QId
,@AId = AId
FROM @Temp2
DELETE
FROM @Temp1
WHERE Id IN (
SELECT DISTINCT #PAOT.ID
FROM #PAOT
WHERE #PAOT.PAOID IN (
SELECT DISTINCT #PAO.PAOID
FROM #PAO
WHERE (#PAO.IsReal = 1)
AND (#PAO.PQID = @QId)
AND (#PAO.PAOID <> @AId)
)
AND (
#PAOT.Id NOT IN (
SELECT DISTINCT #PAOT.Id
FROM #PAOT
WHERE #PAOT.IsReal = 1
AND #PAOT.PAOID = @AId
)
)
)
DELETE
FROM @Temp2
WHERE QId = @QId
AND AId = @AId
SET @Loop -= 1
END
--WHILE LOOP ENDS
--CLEAN UP
DROP TABLE #PAO
DROP TABLE #PAOT
DROP TABLE #EP
October 24, 2013 at 10:00 am
Where did you read that WHILE loops are better than cursors? They're both loops. Far better is to do what you're trying to do in one set-based operation. You've only a few rows of test data at the moment, so you may not notice much different, but as soon as you get thousands or millions of rows, you'll want to eliminate loops and do it all in one go.
John
October 24, 2013 at 10:04 am
Thanks for your response. How do I replace the query above with a set based? What exactly would that look like?
I am not worried about seeing a performance difference (no matter how small) at the moment. I am simply trying to learn how to write set based code and stay away from while loops altogether. That was why I didn't put a lot of data.
Thanks again for your response.
October 24, 2013 at 10:08 am
Could you explain a little more on what you're trying to do? There are no changes on @Temp1 which I suppose is the dataset you want in the end.
October 24, 2013 at 10:19 am
Hey Luiz, thanks for your response.
You are right, the data is really something I just made up and created on the fly, you are right there is no change at the end.
I just created a script that parses and runs successfully. I ran it with my profiler on and saw the baseline.
since it's a while loop, I want to know how I can replace the query in questions with a set based query. Once I see how it's done, (hopefully I get more than one way to do it), i will run all of them with the profiler on and see the best result. This will help me in the future when writing set based code.
of course I will always test all methods to find the most optimal for my current situation in the future.
Thanks for reading.
October 24, 2013 at 10:35 am
sqlislife (10/24/2013)
Hey Luiz, thanks for your response.You are right, the data is really something I just made up and created on the fly, you are right there is no change at the end.
I just created a script that parses and runs successfully. I ran it with my profiler on and saw the baseline.
since it's a while loop, I want to know how I can replace the query in questions with a set based query. Once I see how it's done, (hopefully I get more than one way to do it), i will run all of them with the profiler on and see the best result. This will help me in the future when writing set based code.
of course I will always test all methods to find the most optimal for my current situation in the future.
Thanks for reading.
There is no generic one solution to replace loops or cursors with set based logic. It requires a fundamental mindset change. The change is to start thinking about what you want to do to a column instead of what you what to do to a row. I appreciate that you creating a pretend situation here. If you can generate a test case where something is actually going to happen we can help you figure out a set based replacement.
_______________________________________________________________
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/
October 24, 2013 at 10:36 am
You might also try reading this article. http://www.sqlservercentral.com/articles/62867/[/url]
Many many many times a tally/numbers table can be used in place of looping.
_______________________________________________________________
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/
October 24, 2013 at 10:49 am
Thanks for the reply and the article.
To answer your question, the query is simply trying to delete row by row based on a certain join condition. Its currently using a while loop to accomplish this.
I do understand there won't be just one way to do it, I was just hoping someone would be able to run the code in their environment and rewrite it by getting rid of the while loop using a set based code.
Thanks for the responses so far.
October 24, 2013 at 10:57 am
sqlislife (10/24/2013)
Thanks for the reply and the article.To answer your question, the query is simply trying to delete row by row based on a certain join condition. Its currently using a while loop to accomplish this.
I do understand there won't be just one way to do it, I was just hoping someone would be able to run the code in their environment and rewrite it by getting rid of the while loop using a set based code.
Thanks for the responses so far.
The problem is that the code doesn't actually do anything. It creates a couple of table variables, populates them, then loops through the keys and deletes all the rows.
The set based solution for this problem is this (assuming that any changes happen after you fill those variables):
DELETE FROM @Temp1
DELETE FROM @Temp2
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply