Microsoft® SQL Server® 2008 Bible

Chapter 22. Kill the Cursor!

IN THIS CHAPTER

• Iterating through data

• Strategically avoiding cursors

• Refactoring cursors to a high-performance set-based solution

• Measuring cursor performance

SQL excels at handling sets of rows. However, the current database world grew out of the old ISAM files structures, and the vestige of looping through data one row at a time remains in the form of the painfully slow SQL cursor.

The second tier of Smart Database Design (a framework for designing high-performance systems, covered in Chapter 2) is developing set-based code, rather than iterative code.

How slow are cursors? In my consulting practice, the most dramatic cursor-to-set-based refactoring that I've worked on involved three nested cursors and about a couple hundred nested stored procedures that ran nightly taking seven hours. Reverse engineering the cursors and stored procedures, and then developing the query took me about three weeks. The query was three pages long and involved several case subqueries, but it ran in 3–5 seconds.

When testing a well-written cursor against a well-written set-based solution, I have found that the set-based solutions usually range from three to ten times faster than the cursors.

Why are cursors slow? Very hypothetically, let's say I make a cool million from book royalties. A cursor is like depositing the funds at the bank one dollar at a time, with a million separate transactions. A set-based transaction deposits the entire million in one transaction. OK, that's not ...