none
Cursors vs while loop RRS feed

  • Question

  • Hi,

     

    We know cursors are evil, use lot of memory, adds up tempdb activity, not scalable, hinders concurrency etc...Say if I replace 10 heavily used cursors in OLTP system with while loops how much do I gain if any and how can I measure that. How can I convince my code review DBA to make this change? Does this change help the server?

     

     


    Friday, August 31, 2007 5:00 PM

Answers

All replies

  • As always, it depends. Using a "cursor" or a "while loop" means approaching the problem in a row by row basis. Better try to find a set based solution if possible.

     

     

    AMB

    Friday, August 31, 2007 5:05 PM
    Moderator
  • I have changed in 3 procs to use set-based approach by removing row-by-row processing logic. But in other procs, we can't get away with row-by-row processing as we have to make a call to another SP for each record. And by changing the cursors to while loops, do we gain anything in terms of performance. This article calls to change the cursors to while loops, and other approaches. My question which is in general, do we gain anything by changing the cursors to while loops. Is there a measure I can capture?

     

    Friday, August 31, 2007 5:14 PM
  • The big gains (orders of magnitude) will come by changing cursors to set-based statements as you've done. If you can avoid row-by-row looping (through cursors or otherwise), then you should see some good gains there as well. In SQL Server 2005's CTEs and MARS we've removed some of the remaining need to use cursors and loops. But there are some situations where row-by-row processing still seems to be needed, and performing some non-set-based statement for a set of rows is the primary example .. executing a DBCC command for each database, for example.

    If you find you are calling a stored proc for each row, perhaps you can pass a table containing the rows into the stored proc (perhaps by using a temp table) and then use set-base operations inside the stored proc, but there are times when you just need to call the sp row by row. If, after investigating all the set-based alternatives, you find you really do have to process rows one by one, then cursors are one way of iterating through a set of rows, and they do provide some good functionality with a well-defined behavior and you'll probably use your cursor together with a WHILE loop.

    If you don't use a cursor to hold the rows to process, you'll have to retrieve a single row yourself each time through the loop; that'll probably be more coding for you, increase the potential for more bugs in your code, perhaps be more costly during execution, etc. So the trade-off becomes one of using cursors with a known downside, versus custom code with other potential drawbacks.

    I'd say the "change cursors to while loops" statement oversimplifies the situation and falls way below the "change cursors to set-based operations where possible" primary guideline .. and it's unfortunate that it's at the top of the list in the article you mention.


    Don

    Friday, August 31, 2007 10:22 PM
  • Hi!

    Well, shouldn't "elapsed time" not be the most obvious measure...

    Best regards

    Saturday, September 1, 2007 8:42 PM
  • "change cursors to set-based operations where possible"

    is in fact oversimplifying the situation. There are many instances where this is not possible, and the OP indicated that in his case this was so. Therefore to pontificate on the well know advantages onset based programming is illogical, and one needs to address the needs of the questioner.  As I am in the same boat myself, I know that like him I am disappointed in the response to this question.  I would genuinely like to know the the amount of server resources that are saved by the use of while loops versus cursors, since a se- based program is not possible in my particular case.

    While elapsed time appears good seem to me that there would still be the question of memory usage to be addressed.

    Update:  check out this article. which seems to provide a criteria to use for comparison.

    http://www.techrepublic.com/blog/the-enterprise-cloud/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/


    Edward R. Joell MCSD MCDBA


    • Edited by joeller Monday, September 29, 2014 5:25 PM Update
    Monday, September 29, 2014 5:07 PM
  • "change cursors to set-based operations where possible"

    I think this can and should be achieved in the great majority of situations.

    The need to work row-by-row generally arises when the subsequent set of instructions expects a single row input.

    E.g. as mentioned before, a procedure with parameters that matches a single row. You could change it to accept a table variable from which you could continue to work with set-based operations.

    Way I see it, you are only restricted to working row-by-row when you cannot change the input of the following process. You cannot, for example, issue a BACKUP DATABASE statement that backs up a set of databases at once, and you cannot CHANGE the backup command to work as you please. You would have to loop through a list of databases and call the statement dynamically for each.

    Monday, September 29, 2014 5:28 PM

  • I'd say the "change cursors to while loops" statement oversimplifies the situation 


    Normally lot of people consider WHILE loops to be more performant than CURSORs just because they have read "Cursors are slow and one should go for set-based approach", thus as a last resort they don't consider CURSORs but end up with WHILE loops.

    Cursors are designed to perform more optimally compared to WHILE loops. Cursors provide you lot of options to use them optimally, check this blog post for more details: http://sqlwithmanoj.com/2011/02/07/avoid-cursors-or-use-them-optimally/

    But YES they are not a good choice while dealing with SET-based operations and should be kept for last resort when you've to process records row-by-row.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page


    Monday, September 29, 2014 5:33 PM
  • Cursors are designed to perform more optimally compared to WHILE loops.


    Usually though, we do use WHILE loops with cursors:

    http://www.sqlusa.com/bestpractices2005/doublecursor/

    I guess one could use GO TO-s with IF...ELSE, but I have never seen it.

    Can somebody build a better mousetrap with WHILE loop as opposed to CURSOR WHILE loop?  Possibly.

    BOL example for nested cursors contain 2 WHILE loops: http://msdn.microsoft.com/en-us/library/ms180169.aspx




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Monday, September 29, 2014 5:45 PM
    Moderator
  • Check this article by Brad Schulz

    http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html

    In short, there should be no gain. You need to use correct options for the cursor declaration. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 29, 2014 8:58 PM
    Moderator
  • Way I see it, you are only restricted to working row-by-row when you cannot change the input of the following process. You cannot, for example, issue a BACKUP DATABASE statement that backs up a set of databases at once, and you cannot CHANGE the backup command to work as you please. You would have to loop through a list of databases and call the statement dynamically for each.

    Or when the stored procedures used to carry out the subsequent process belong to another person's database where you only have rights to execute selected SPs, which were designed to select the input of one row of data at a time.

    Edward R. Joell MCSD MCDBA


    • Edited by joeller Friday, October 31, 2014 2:29 PM
    Friday, October 31, 2014 2:29 PM