- Introduction
- Optimizing Loops and Cursors
- Temporary Tables and Table Variables
- Performance Issues with Views
- The Effect of Triggers
- Query Hints and Options
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Optimizing Loops and Cursors
T-SQL is a set-based language, optimized for joining tables together, reading and writing data. This fact is sometimes difficult to get used to, especially if you're familiar with other procedural languages such as Visual Basic, C++, PowerBuilder, and so on. In fact, if you're a VB programmer probably the first thing you learned to do was to loop through the records. While this might work really well in the front-end and middle-tier programs, T-SQL loops are usually slow. Fortunately, you can write most T-SQL programs without using loops or cursors.
Cursors are a temporary storage area for a result set that your program will iterate through and do something for every record in the cursor. Although cursor code is fairly straightforward to write, it doesn't perform very well. One of my tuning engagements involved optimizing a scrubbing program that appended a random character and the current date and time at the millisecond level to every row of a table with millions of records. As you might imagine, going through millions of rows, one row at a time, was taking a very long time. I suggested appending a current date and time along with a random character and keeping the first character of the existing string. The mission of the program was still accomplished—the end users couldn't read the un-scrubbed data—but my program executed 100 times faster since I only had to use a single UPDATE statement.
Occasionally you have no choice but to use cursors. In such cases, try to optimize the statement that populates the cursor. The bottleneck of the cursor is often the SELECT statement that grabs all records satisfying the criteria and sticks them into a cursor.
Optimizing loops in T-SQL is similar to optimizing them in other languages. You should try to do as much outside of the loop as you can. For instance, if you're declaring variables and initializing their values inside the loop, your program will do this same operation each time you run through the loop. Alternatively, you could have declared and initialized the variables once, before going into the loop.