May 25, 2010 at 6:28 am
Hi.
I've heard and read so much negative press about cursor performance and that we should never use cursors but rather set based queries. A couple of questions then if I may;
a. If a cursor only retrieves say 50 rows, is there still a memory-hit on the database server every time a stored procedure is executed that contains such a cursor ?
b. Is it legitimate to consider replacing a cursor with a While.. loop, performing a select query inside the loop ?
Thanks.
May 25, 2010 at 7:27 am
a) yes there is still a performance hit for using cursors over a single select statement to retrieve your 50 rows.
b) a while loop is still processing the data row by row and is not optimal for performance, you should aim to write code that processes the data in sets rather than loops
May 25, 2010 at 7:41 am
The performance hit is still *there* for a small number of rows, it's just much harder to see because you're probably dealing with milliseconds. The biggest problem with non set based code is that it doesn't scale. So what is only a few ms on 50 rows becomes a few seconds on 500 which may become minutes or hours on thousands and millions.
As Steve points out, it's not the cursor itself that's bad, it's the fact that you're doing things in a 'loop'. In many cases, a cursor can be just as efficient (or more efficient) than a while loop. It is very rare (although it does happen in specific instances) for either of these methods to beat set based code.
May 25, 2010 at 7:45 am
Useful, informative, thanks guys!
May 25, 2010 at 8:29 am
Just to add to what has already been said...
There are a lot of places where well meaning folks use Cursors/While Loops because a job "can't be done using set based code". That's normally an incorrect understanding, though. Yes, there are places where only a Cursor/While Loop would be the best way to do something but they are a whole lot more rare than people would have you believe especially since SS 2k5 came out.
I'll also warn you that you just because something doesn't have an explicit loop in it doesn't necessarily mean that it's set based. There are exceptions, of course, but most forms of recursion like recursive CTE's can be as slow as cursors and worse on the I/O system (logical and/or physical).
Finally, there are a lot of examples where people claim that set based code is worse than a loop. A lot of times, the "set based" code they've written isn't actually set based. It'll sometimes have "Hidden RBAR" such as Triangular Joins or recursion in it. "Set based" doesn't simply mean "absence of an explit loop".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply