Press "Enter" to skip to content

Digging into Cursors

Hugo Kornelis gives us a primer on cursors:

And yes, I know the mantra. Do not use cursors. They are slow. There is (almost) always a faster set-based alternative available. So why would I even waste blog space on cursors, when the only smart thing to do is to rip them out and replace them with a set-based alternative?

Well, there are, in fact, many reasons. The “almost” above suggests that there are still cases where row by row processing is in fact the most efficient method. There are cases where we use a cursor, even though set-based is slower, because we need to call a stored procedure for each row returned, and that stored procedure is too complex to be changed to process an entire set at once. Or, perhaps, you just inherited existing code that uses a cursor, and you need to fix the immediate performance issues now, so you cannot afford the time investment to rewrite the row by row logic to set-based logic.

Another example of this was calculating running totals prior to SQL Server 2012’s support of aggregate window functions. The cursor approach was considerably faster than the self-join approach because the self-join approach required joining on an inequality statement. Granted, there was the “quirky update” technique, but that depended on an accident of SQL Server internals that Microsoft never officially supported and could have broken at any time.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.