March 2010 Archives

T-SQL Cursor Performance Optimization

March 7, 2010

One should work hard to avoid Cursors in T-SQL – for instance, by using recursive common table expressions &ndsh; but when you need to call a separate stored procedure in a loop, you're stuck with them. I recently came across an excellent guide to the subject, Performance Tuning SQL Server Cursors, which includes the following enlightening piece of advice:

If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server. If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.

Every cursor I've ever written would have benefited from this.

About this Archive

This page is an archive of entries from March 2010 listed from newest to oldest.

February 2010 is the previous archive.

April 2010 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Categories

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.12