The Curse of Cursor Options

Red Skies At Night

I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix.

Sometimes there’s a decent reason, something like: We have a query that generates a really big result, and we need to update another table with it. When we do it without the cursor, there’s bad locking, it runs for a long time, the transaction log gets huge, etc.

Not bad reasons! I still think there are better ways to do that. But hey.

Not So Fast Forward

When you write cursor code, there are a bunch of options you can choose. One of them is FAST_FORWARD. It’s documented, ahem, thusly:

FAST_FORWARD
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

Friends, Lulu Romans, Countryhams.

OPTIMIZATIONS ARE ENABLED!

We don’t know what they are, but they’re there.

Feed Me, Seymour

Let’s say we have a pretty “big” query to feed our cursor. When we write it and validate the logic, it looks like this:

And the query plan looks like this:

I’m not an animal

It goes parallel, as we’d expect a “big” query to do, and runs for 3 seconds. I’m not saying there’s nothing we could do here, but let’s roll with it for now.

Let’s Write Some Code!

First, we need to introduce a new table. It’s gonna hold our high scores.

Now we’re gonna write a stored procedure that’s gonna do a few things.

First Thing

We’re gonna check our high score table for any new Users with a Reputation over 1000

Second Thing

We’re going to declare our variables for the variable gods, and open a cursor with OPTIMIZATIONS ENABLED to make sure our optimizations are optimized.

Third Thing

We’re gonna update the high score table with new information. This looks a little silly, but people can change their names on the site.

Runtime!

Now we’ll run our stored procedure. We’re very cool people, after all.

The first thing we’ll notice is that it runs for about 23 seconds.

If we then, as very cool people do, examine the query plan for our procedure, we’ll notice some oddities.

The first is that, well, our “big” query to feed the cursor doesn’t go parallel anymore.

Unlovable

Warnings

sp_BlitzCache warns us about this in two ways.

This warning comes from the XML, which is also pretty explicit:

If we go and run our feeder query at MAXDOP 1…

We’ll be saddened to learn that our Three Second Masterpiece® has turned into an Eleven Second Turd®

Better Options?

Cursor options like FORWARD_ONLY, STATIC, and KEYSET can all produce parallel plans, and reduce the total proc runtime to about 15 seconds — remember that it was around 23 seconds with the FAST_FORWARD cursor, with OPTIMIZATIONS ENABLED!

Some optimizations, there, pal.

Thanks for reading!

Previous Post
Save 50-75% Now: The 2018 Black Friday Sale is On!
Next Post
[Video] Office Hours 2018/10/31 (With Transcriptions)

8 Comments. Leave new

  • Thank you! Very helpful… and you are so right about that dungeon code. It’s everywhere!!

    Reply
  • Alex Friedman
    November 4, 2018 3:06 am

    Interesting! Aaron Bertrand compared several performance metrics of different cursor options years ago, recommending LOCAL FAST_FORWARD, though I don’t think he looked specifically at parallelism.

    https://sqlperformance.com/2012/09/t-sql-queries/cursor-options

    Reply
    • Alex — yep, there are a lot of cursor options, and a lot of things to look at.

      I generally agree with Aaron that the defaults aren’t good, but the query he used isn’t eligible for parallelism anyway because of the system objects it’s touching.

      Thanks!

      Reply
  • It would be a great idea to move past the hysteria over cursors. Like any other tool, they can be used well or badly. They are NOT inherently evil. In fact, they are perfect for working your way through a list of repetitive operations.

    Reply
    • Tom — that was kind of my point. Cursors and loops have their place, but people should be careful about how they’re called.

      Thanks!

      Reply
      • I think it’s important to keep in mind a cursor’s place. Cursors were built to go through data stored on a tape drive. If I’m traversing data on tape, I use a cursor (though the occasion for this hasn’t come up lately, go figure). If I’m looping through data in an “array” I use a WHILE loop. I’ll leave the debates on when looping is and isn’t the best solution to different thread topics.

        Reply
  • Erik: it should be noted that by default, CURSORs are “sensitive” / dynamic. Meaning, they are sensitive to changes made in the base tables of the cursor-based query. This is why there exists the @@FETCH_STATUS of -2 (row is missing). I assume that this also causes the cursor to maintain at least light-weight locks on the base tables. This is then why STATIC cursors are typically faster since they copy the result set to an internal temp table, and don’t re-query or lock base tables. And thus STATIC cursors are considered “insensitive” (“sensitive” being the ANSI semantics).

    That being said, I use the following options unless a situation requires something slightly different (e.g. not specifying STATIC when the query only deals with temporary tables since there is no point in re-copying non-shared data):

    STATIC LOCAL READ_ONLY FORWARD_ONLY
    ——————————————
    To Jason Smith’s point of “I’ll leave the debates on when looping is and isn’t the best solution to different thread topics.”:

    There isn’t much debate here: WHILE loops are inherently slower AND more error prone (e.g. forgetting to increment the index or forgetting to “remove” the current row when finished with it, depending on your method of moving through it). I have tested this several times, and so have many others. Bottom line is: the CURSOR mechanism is internally optimized to efficiently move through the list it is given, and no matter how you structure your while loop, it will always do more reads and/or writes than a CURSOR created with the proper options (i.e. STATIC READ_ONLY FORWARD_ONLY).

    Reply
    • The thing I’m most guilty of when writing my own while loops is forgetting to reset some variables when part of a loop is finished. In other words, my DBA is showing. Heh.

      Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}