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:
|
1 2 3 4 5 6 7 8 |
SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId WHERE p.PostTypeId = 1 AND p.Score > 0 AND u.Reputation > 1000 GROUP BY u.Id, u.DisplayName; |
And the query plan looks like this:
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.
|
1 2 3 4 5 6 7 |
CREATE TABLE dbo.HighQuestionScores ( Id INT PRIMARY KEY CLUSTERED, DisplayName NVARCHAR(40), Score BIGINT ); GO |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROCEDURE dbo.curses AS BEGIN SET NOCOUNT ON; INSERT dbo.HighQuestionScores (Id) SELECT u.Id FROM dbo.Users AS u WHERE u.Reputation > 1000 AND NOT EXISTS ( SELECT 1/0 FROM dbo.HighQuestionScores AS h WHERE h.Id = u.Id ); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @Id INT; DECLARE @DisplayName NVARCHAR(40); DECLARE @Score BIGINT; DECLARE crap CURSOR FAST_FORWARD READ_ONLY FOR SELECT u.Id, u.DisplayName, MAX(p.Score) AS TopScore FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId WHERE p.PostTypeId = 1 AND p.Score > 0 AND u.Reputation > 1000 GROUP BY u.Id, u.DisplayName; OPEN crap; FETCH crap INTO @Id, @DisplayName, @Score; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
FETCH crap INTO @Id, @DisplayName, @Score; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE h SET h.DisplayName = @DisplayName, h.Score = @Score FROM dbo.HighQuestionScores AS h WHERE h.Id = @Id FETCH crap INTO @Id, @DisplayName, @Score; END; CLOSE crap; DEALLOCATE crap; END GO |
Runtime!
Now we’ll run our stored procedure. We’re very cool people, after all.
|
1 |
EXEC dbo.curses; |
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.
|
1 |
EXEC sp_BlitzCache @StoredProcName = 'curses' |
The first is that, well, our “big” query to feed the cursor doesn’t go parallel anymore.
Warnings
sp_BlitzCache warns us about this in two ways.
This warning comes from the XML, which is also pretty explicit:
|
1 |
NonParallelPlanReason="NoParallelFastForwardCursor" |
If we go and run our feeder query at MAXDOP 1…
|
1 2 3 4 5 6 7 8 9 |
SELECT u.Id,u.DisplayName, MAX(p.Score) AS TopScore FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = p.OwnerUserId WHERE p.PostTypeId = 1 AND p.Score > 0 AND u.Reputation > 1000 GROUP BY u.Id, u.DisplayName OPTION(MAXDOP 1); |
We’ll be saddened to learn that our Three Second Masterpiece® has turned into an Eleven Second Turd®
|
1 2 |
SQL Server Execution Times: CPU time = 10891 ms, elapsed time = 11459 ms. |
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!
12 Comments. Leave new
Thank you! Very helpful… and you are so right about that dungeon code. It’s everywhere!!
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
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!
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.
Tom — that was kind of my point. Cursors and loops have their place, but people should be careful about how they’re called.
Thanks!
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.
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).
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.
“First Thing” SP is missing an “END”.
Ah, missed that it’s split over a few windows.
In my opinion the biggest drawback of cursors is that they are by default global out of the box, so if close and deallocate the cursor doesn’t happen it stays open. I always declare them local and static or I don’t use them.
Good call.