Use Of Cursor Vs Simple While Loop

  • Comments posted to this topic are about the item Use Of Cursor Vs Simple While Loop

    Thanks.

  • 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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • 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

  • 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.

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • 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.

  • 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.

  • 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. 😉


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 24 total)

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