I've written several times about using cursors and how, in most cases, it is more efficient to re-write your cursors using set-based logic.
I'm realistic, though.
I know that there are cases where cursors are "required" – you need to call another stored procedure or send an e-mail for every row, you are doing maintenance tasks against each database, or you are running a one-off task that simply isn't worth investing the time to convert to set-based.
How you are (probably) doing it today
Regardless of the reason you are still using cursors, you should at the very least be careful not to use the quite expensive default options. Most folks start their cursors off like this:
DECLARE c CURSOR FOR SELECT whatever FROM ...
Now again, for ad-hoc, one-off tasks, this is probably just fine. But there are…
Other ways to do it
I wanted to run some tests using the defaults and compare them to different cursor options such as
FAST_FORWARD. (There are a ton of options, but these are the ones most commonly used as they are applicable to the most common types of cursor operations that people use.) Not only did I want to test the raw speed of a few different combinations, but also the impact to tempdb and memory, both after a cold service restart and with a warm cache.
The query I decided to feed to the cursor is a very simple query against
sys.objects, in the AdventureWorks2012 sample database. This returns 318,500 rows on my system (a very humble 2-core system with 4GB RAM):
SELECT c1.[object_id] FROM sys.objects AS c1 CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2;
Then I wrapped this query in a cursor with various options (including the defaults) and ran some tests, measuring Total Server Memory, pages allocated to tempdb (according to
sys.dm_db_session_space_usage), and total duration. I also tried to observe tempdb contention using scripts from Glenn Berry and Robert Davis, but on my paltry system I could not detect any contention whatsoever. Of course I'm also on SSD and absolutely nothing else is running on the system, so these may be things you want to add to your own tests if tempdb is more likely to be a bottleneck.
So in the end the queries looked something like this, with diagnostic queries peppered in at appropriate points:
DECLARE @i INT = 1; DECLARE c CURSOR -- LOCAL -- LOCAL STATIC -- LOCAL FAST_FORWARD -- LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT c1.[object_id] FROM sys.objects AS c1 CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2 ORDER BY c1.[object_id]; OPEN c; FETCH c INTO @i; WHILE (@@FETCH_STATUS = 0) BEGIN SET @i += 1; -- meaningless operation FETCH c INTO @i; END CLOSE c; DEALLOCATE c;
Quite arguably the most important and common measure is, "how long did it take?" Well, it took almost five times as long to run a cursor with the default options (or with only
LOCAL specified), compared to specifying either
I also wanted to measure the additional memory that SQL Server would request when fulfilling each cursor type. So I simply restarted before each cold cache test, measuring the performance counter
Total Server Memory (KB) before and after each test. The best combination here was
This result was surprising to me. Since the definition of a static cursor means that it copies the entire result to tempdb, and it is actually expressed in
SNAPSHOT, I expected the hit on tempdb pages to be higher with all static variants of the cursor. This was not the case; again we see a roughly 5X hit on tempdb usage with the default cursor and the one with only
For years I have been stressing that the following option should always be specified for your cursors:
LOCAL STATIC READ_ONLY FORWARD_ONLY
From this point on, until I have a chance to test further permutations or find any cases where it is not the fastest option, I will be recommending the following:
(As an aside, I also ran tests omitting the
LOCAL option, and the differences were negligible.)
That said, this is not necessarily true for *all* cursors. In this case, I am talking solely about cursors where you're only reading data from the cursor, in a forward direction only, and you aren't updating the underlying data (either by the key or using
WHERE CURRENT OF). Those are tests for another day.