Script Performance Increase with CURSOR Parameters

Recently I attended the PASS Summit 2013 and found an interesting performance tip with the CURSOR statement. Cursors are not always the best choice but when you have to use one; check out the parameters that can be used.

Below is part of my original cursor that I used in one of my script. The total script executed in 6 seconds

…
DECLARE myInstances CURSOR FOR
SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
….

Making the following changes by adding LOCAL FAST_FORWARD after the CURSOR statement.

 …

DECLARE myInstances CURSOR LOCAL FAST_FORWARD FOR
SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
..

By adding this change, the same script executed in 3 seconds. That’s a 50% improvement in performance.

Books On Line and the following links provide more details as on this and additional parameters you can use with the CURSOR statement. 

Increase your SQL Server performance by replacing cursors with set operations

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

Understanding SQL Server Fast_Forward Server Cursors
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx

Comparing cursor vs. WHILE loop performance in SQL Server

http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741

Curious cursor optimization options

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
 
Have a look at the above links and try some testing of your own. You too will be surprised at the performance increase you can get. My own testing showed that I can get from 10% - 70% performance increase with simple changes.

Ensure you test these parameters fully before applying to your production server.

Thanks,

Rudy