Next week as part of the free SQL Query training that Aaron Buma and I provide, the topic I will be presenting is cursors. How to use them, what they are, everything you ever wanted to know.
My concern in this presentation is that cursors have caused me many headaches in regard to performance over time.
The main problem that I see is that developers who generally think in programming constructs like looping, like to use cursors because it is comfortable, it is more like a WHILE loop iterating through a result set. But SQL Server is designed manage sets of data through joining very quickly, and iterating through a loop generally ends up being much slower that joining. The term RBAR or Row By Agonizing Row describes this slow process.
I am looking for other arguments as to why cursors are good or bad. I know the final answer is always, “It Depends”, but what are some cases that cursors work well, and what are areas that they are horrible? I am looking for some stories here to help provide a good foundation for the weekly training.
Please post your response below.
Thanks!
-Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
The “good” thing about cursors are that they scale almost lineary. They suck at performance, but at least they suck linearly :) If it takes 1 minute for 100,000 rows, you can expect about 10 minutes for 1,000,000 rows. Some set based solutions seems faster, but don’t scale that well. They are faster than cursors for small data sets, but suddenly get real bad when you use them over millions of records. Luckily window functions solved a lot of those issues.
In the T-SQL Window functions book by Itzik Ben-Gan, he describes different use cases for window functions and he usually provides alternative solutions as well. I remember one use case where the cursor wasn’t that bad after all (but the window function was still better).
Koen – Thanks for the awesome response. I appreciate it.
-Steve