THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Curious cursor optimization options

This blog has moved! You can find this content at the following new location:

Published Wednesday, November 21, 2007 2:15 AM by Hugo Kornelis

Attachment(s): Cursor comparison.xls

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



a.m. said:

How about the fastet possible cursor: One built using SQLCLR..?  A loop over a SqlDataReader is much faster, in my tests, than any T-SQL cursor.

November 20, 2007 8:01 PM

Hugo Kornelis said:

Note to all - I just spotted an annoying typo. At one place, I had written FAST_FORWARD where I actually meant FORWARD_ONLY.

This is now fixed.

November 23, 2007 8:22 AM

JJ B said:

Very informative!  I hardly ever use cursors, but if I ever have to in the future, this article would prove very useful.  Thanks for doing the research.  (I did read and understand the part about my mileage varying...)

November 26, 2007 11:46 AM

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

After making my post on cursor optimization I received some comments that triggered me to do some further

November 27, 2007 5:47 PM

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' said:

Some problems can only be solved by brute-forcing every possible combination. The problem with such an

November 30, 2007 4:58 PM

kzimmerm said:

Thanks for the information.  I was able to come up with a somewhat faster sproc which utilizes a cursor.  I was able to squeeze out a better performing procedure with little effort.

Thanks again.

December 19, 2007 1:47 PM

Ben H said:

Execellent article, thankyou

July 9, 2008 8:01 PM

Anil Desai said:

Hi i want to use "CURRENT OF" in cursor which only print one column row by row.

Here i make one table named "Student" which contain columns (studentId,studentName).

Than i create one T-Sql code like....



  SELECT studentId, studentName FROM Student

OPEN mycursor

FETCH mycursor



select studentName from Student

where CURRENT OF mycursor

  FETCH mycursor


deallocate mycursor


is it possible to use "CURRENT OF" like above code?


July 29, 2008 3:28 AM

Akki said:

Hi Thanks a lot for this info.this was really useful.

i did get to know where and how to improve performance using cursor.

Thanks a lot


December 14, 2009 5:44 AM

Akki said:


I have a question. dos'nt this performance mainly  depends on the server space and other server related things ??


December 14, 2009 5:49 AM

Hugo Kornelis said:

Hi Akki,

Performanhce depends on many factors. Available memory, disk setup, amount of processors, and various other server options are all very important.

However, many of the options described in this article change the way SQL Server accesses the data. And differrent ways produce different speeds.

Bottom line: If you add memory to your server and buy a better disk subsystem, all cursors will speed up, but thosse with the "better" options will still perform better than those with the "slower" options.

One caveat - after posting this article, I found that there are cases where the FAST_FORWARD option is faster than the STATIC option. In situations where all the data to be processed by the cursor fits into the cache, STATIC always wins. In cases where the amount of data is way too large to fit into cache, FAST_FORWARD has the edge.

Best, Hugo

December 14, 2009 6:13 AM

Aaron Bertrand said:

This week, T-SQL Tuesday is being hosted by Jes Borland ( blog | twitter ), and the theme is " Aggregate

March 8, 2011 4:46 PM

Anonymous said:

Thanks, your article helped me and many others no doubt.

July 26, 2013 2:34 PM

Bheemsen Singh said:

Very useful....!!

Thanks a lot for this info.this was really useful.

I use cursors, but this article very useful.

May 14, 2014 2:47 AM

Eric S said:

Thank you for the great article. I was doing some speed improvements on a Cursor and assumed that FAST_FORWARD was the fastest. Switching to STATIC improved speed by only 5%, but I'll take it!

February 26, 2015 11:22 AM

john busciglio said:

I have a cursor that inserts 250k records into a table, grabs the scope_identity from the current record and updates another table with that id.

With the defaults cursor option it takes nearly 7 MINUTES to run that cursor insert/update scope_identity.

after reading your article and testing out the different ways to run this, I found that the following cursor options worked best for me.


14890 millisecs.

Great article, thank you!

BTW, FOR_UPDATE had 0 change in execution time compared to the cursor default.  go figure right?  

August 24, 2015 11:50 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement