Honk Honk
People often trash cursors even when they’re used for perfectly fine reasons.
I understand that this reaction may be because they’ve seen cursors misused in the past. Sometimes because they heard someone popular say it.
In either case, everything has a time and place, and there are many times when cursors aren’t the performance sucks people chalk them up to be.
Reasonable Uses
Places where cursors don’t freak me out:
- Maintenance scripts (backup, checkdb, etc.)
- Building dynamic strings
- Batching modifications
- Passing per-thing parameters to a stored procedure
It might shock you to find cursors in well-respected pieces of code, like sp_WhoIsActive. But if you crack open the proc and search for “cursor”, you’ll find six of them that do different things. Do you still hate cursors?
What if I showed you Paul White his-very-self suggesting people use them?
- Rejoining range seek on nullable composite index?
- Most cost efficient way to page through a poorly ordered table?
- Calculating the Median with a Dynamic Cursor
Not to mention other luminaries and nobodies who have found reason to call upon the cursed cursors.
So What Then?
Should you start out most code by writing a cursor? Absolutely not.
Should you convert every cursor to a while loop? Ehhhhh.
Should you understand when you should or shouldn’t use a cursor? Absolutely.
Some people have had pretty good careers talking about knee-jerk reactions, and I think seeing a cursor declared illicits many knee jerk reactions.
Read the code. Understand the requirements.
I tune queries all day long. The number of times someone has said THIS CURSOR IS A REAL BIG PROBLEM and been right is pretty small.
Often, there was a tweak to the cursor options, or a tweak to the query the cursor was calling (or the indexes available to it) that made things run in a more immediate fashion. I want to tune queries, not wrestle with logic that no one understands. Old code is full of that.
The number of times I’ve seen someone tell me they made something faster with totally broken logic and incorrect results is pretty high.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
In all fairness, I’ve found if I return the wrong results, and broken most of my logic, that it actually does return fairly fast. Wrong, but performant.
Sometimes it’s nice to see what could be though 😀