December 6, 2003 at 7:29 am
It's said that cursors are overused, and from my experience I'd say that's true. Often a set based method can be used much more efficiently.
However, sometimes the only method I can think of to replace the cursor is via a while loop, which seems cursor-like to me.
Which is preferred? How far should one go to avoid cursors?
Data: Easy to spill, hard to clean up!
December 6, 2003 at 12:27 pm
while loop because SQL Server is optimized to run set based operations. Even running them many times will often be better than a cursor. Of course, you'd have to benchmark this on your system.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 6, 2003 at 12:58 pm
This is a very tricky question my answer is test, test and test.
There are "some" cases where the cursor in the winner
* Noel
December 8, 2003 at 3:05 pm
I agree that benchmarking needs to occur. Sometimes there is an occasion where a cursor actually performs better.
We had a case here where the IO and CPU time was actually lower than While Loops.
December 11, 2003 at 1:09 pm
Havent tested, but dont know that I'd agree. The problem with the while is you're continuously reevaluating the condition, which maps out to the worst type of cursor - dynamic.
I always try to solve set based first, but if I need a cursor, why not use what is built in? Rather than code to deal with issues like rows being changed in mid loop, the cursor can handle it for you.
Andy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply