March 30, 2015 at 12:00 pm
Comments posted to this topic are about the item Use Of Cursor Vs Simple While Loop
Thanks.
April 20, 2015 at 4:49 am
I try to avoid both cursors and while loops at all costs. I find them both quite expensive in terms of performance. unless forced to because of what is happening in the body of the loop, I would rewrite the cursor or a while loop as set based operation, perhaps using a tally table.
Gerald Britton, Pluralsight courses
April 20, 2015 at 6:35 am
There might be the odd special situation where this is a good idea and will work but from what I can see you need to rely on there being a contiguous incrementing set of integer values to do the select on and you are relying the the records not being updated from the beginning of the loop to the end of the loop. They are also both simple WHILE loops.
April 20, 2015 at 9:29 am
A cursor and a while loop are both slow loops. I avoid them like the plague except when absolutely necessary.
In looking at the example, the loop approach takes a total of 19 reads to return 4 rows. A simple SELECT statement fired against the table takes 1 read. I know this is just an example and is working against a relatively small table, but what do you think would happen if you ran it against a 1-million row table?
I'll continue to use set-based options except when absolutely necessary.
April 20, 2015 at 10:21 am
Great Discussion Topic.
Both scripts are useless as SQL. This is a great example of CURSOR, FLOW Control, PRINT and general SQL misuse.
Never use an object until you understand the use case for the object.
Would you use a single string that is a comma separated list of strings instead of an array?
The largest problem with CURSOR use is lazy development by those that want SQL to be a language like VB or C .
Line value based languages built to examine distinct singular objects and values.
These are not languages to be used on Sets of objects with Sets of Values like SQL.
IMHO - Both the while and the cursor scripts you have here are not really SQL.
They are something else pretending to be SQL by wearing an SQL Suit.
I have never seen the Print command in production SQL code.
I have seen it in way to many Programing example applications.
SQL looks like this -
SELECT ' Linked Server DETAILS- ' + CONVERT(Varchar(max), a.name) FROM sys.servers a WHERE is_linked ='1'
SQL returns data in a Record Set of column and rows. Not the output of a print command to the Message Object.
ROW or Value based DML is not for logical flow control.
DML will always work with SETS of data, even when inspecting it at the ROW level.
If you are returning data to any output ROW by ROW, that is not SQL that is a VB script structure or maybe error handling.
April 20, 2015 at 10:49 am
Jonathan AC Roberts (4/20/2015)
There might be the odd special situation where this is a good idea and will work but from what I can see you need to rely on there being a contiguous incrementing set of integer values to do the select on and you are relying the the records not being updated from the beginning of the loop to the end of the loop. They are also both simple WHILE loops.
I noticed the same thing.
April 20, 2015 at 10:54 am
Wow. There seems to be a lot of bad feelings for Cursors and While loops out there. There are some cases where there will never ever be a million rows of data.
April 20, 2015 at 11:17 am
Iwas Bornready (4/20/2015)
Wow. There seems to be a lot of bad feelings for Cursors and While loops out there.
Justified, for the most part.
Gerald Britton, Pluralsight courses
April 20, 2015 at 11:40 am
g.britton (4/20/2015)
Iwas Bornready (4/20/2015)
Wow. There seems to be a lot of bad feelings for Cursors and While loops out there.Justified, for the most part.
Yes, it's justified. So if you don't have a million rows, does that mean it performs well? Run it over 100,000 rows. Run it over 10,000 rows. Run it over 1,000 rows. Whatever number you pick, race it against a set-based solution and observe the results. Running it over a larger number of rows tends to expose weakness.
It's about both performance scalability. Almost anything should perform decently over 10 rows, but we all know data tends to grow. When it reaches that tipping point and performance tanks, you'll be off on a wild goose chase trying to find out what changed. When you realize no code has changed, you'll be left searching for the junk code that's causing the problem. Of course, the DBA should be able to identify the problem queries that never should have made it into production in the first place.
Do I have "bad feelings" regarding cursors? Definitely. They're hogs.
Start here: http://www.sqlservercentral.com/articles/T-SQL/62867
April 20, 2015 at 12:11 pm
Ed Wagner (4/20/2015)
g.britton (4/20/2015)
Iwas Bornready (4/20/2015)
Wow. There seems to be a lot of bad feelings for Cursors and While loops out there.Justified, for the most part.
Yes, it's justified. So if you don't have a million rows, does that mean it performs well? Run it over 100,000 rows. Run it over 10,000 rows. Run it over 1,000 rows. Whatever number you pick, race it against a set-based solution and observe the results. Running it over a larger number of rows tends to expose weakness.
It's about both performance scalability. Almost anything should perform decently over 10 rows, but we all know data tends to grow. When it reaches that tipping point and performance tanks, you'll be off on a wild goose chase trying to find out what changed. When you realize no code has changed, you'll be left searching for the junk code that's causing the problem. Of course, the DBA should be able to identify the problem queries that never should have made it into production in the first place.
Do I have "bad feelings" regarding cursors? Definitely. They're hogs.
Start here: http://www.sqlservercentral.com/articles/T-SQL/62867
+1 to all the above.
or should I say +1 to 100 million to all the above.
April 21, 2015 at 1:48 am
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less.
My take on this:
1. Set-based is (almost) always faster than iterative. Avoid both WHILE and CURSOR whenever you can.
2. If you do have to use looping, then use the best tool for the job. And that is *not* a WHILE loop. A cursor outperforms a WHILE loop - when used appropriately. Which means that you have to supply some options, because the default options suck.
The comment in the article on forgetting to DEALLOCATE is similar to saying not to use WHILE loops because sometimes people forget to add the SET @i += 1 or whatever else is used to move to the next, and then you get an infinite loop. Those are programming errors that will be caught during peer review and testing.
Sources:
Performance of cursor with standard options versus "good" options: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
Performance of cursor vs while loop: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/28/poor-men-see-sharp-ndash-more-cursor-optimization.aspx
The specific script in the article is unreliable as a replacement for a cursor. It depends on a column with incrementing values being present in the query results, which is not always the case. The example given relies on sys.servers containing only a row for the current server with server_id equal to 0, and all other rows containing linked servers and having server_id numbered consecutively and ascending. If you have for instance three linked servers and then delete the first, will the others be renumbered, or will you simply have rows for servier_id 0 (not linked), and 2 and 3 (linked)? In the latter case, you will get incorrect results from the WHILE example, but the cursor will still work.
This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
April 21, 2015 at 7:04 am
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
April 21, 2015 at 7:38 am
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
I understand the point you're making, but would like to point our, for the benefit of anyone less experienced than yourself, the ROW_NUMBER function is not actually there for the purpose of adding step counters to result sets to make it easier to use cursors. Best advice is still Just Say No To Cursors (although my maintenance routines are full of them - they are actually iterative and not set based processes though)
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 21, 2015 at 7:45 am
andrew gothard (4/21/2015)
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
I understand the point you're making, but would like to point our, for the benefit of anyone less experienced than yourself, the ROW_NUMBER function is not actually there for the purpose of adding step counters to result sets to make it easier to use cursors. Best advice is still Just Say No To Cursors (although my maintenance routines are full of them - they are actually iterative and not set based processes though)
You are right. Hopefully nobody would have read my comment (and the link) about using ROW_NUMBER to create a ROW_ID column in a result set and think I meant that this was a good reason to create a terrible VB script structure using SQL. The iterative while loop is just the tip of the terrible code Iceburg.
April 21, 2015 at 7:47 am
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
If you intend to add a ROW_NUMBER() to the query inside the WHILE loop of the code in the article, then ... please don't. SQL Server will have to evaluate the ROW_NUMBER() every iteration of the function. For the first few iterations you MAY find yourself lucky and get a plan that only finds the first n rows, but even then, you are wasting a huge amount of performance.
If you intend to use ROW_NUMBER() instead of identity to populate the temporary table, then again ... please don't. IDENTITY works just as well for this purpose, but faster. The difference is not as big as with using ROW_NUMBER() in the loop, but there still is a difference.
By the way, this whole discussion is rather theoretic, since the best option is to avoid iteration at all, and the second best iteration is to use a cursor with the correct options (FAST_FORWARD if the data size may exceed available cache; STATIC READ_ONLY if the data will always be small enough to fit in cache even when the system is under load). So you would never use this option anyway. 😉
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply