cursors vs while loop

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?

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

|||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?

|||

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

|||

Hi!

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

Best regards