Row-by-Row Processing - SQL Server Query Performance Tuning - page 469

Figure 22-4. Profiler trace output showing the total cost of the data processing using an equivalent SELECT statement

In Figure 22-4 , you can see that the second execution of the stored procedure, which reuses the existing plan,
uses a total of 597 logical reads. However, you can see a result even more important than the reads: the CPU time used
drops from 156 milliseconds in the first query to 31 milliseconds in Figure 22-4 , and the duration falls from 682ms to
75ms. Using SQL queries instead of the cursor operations made the execution nine times faster.
Therefore, for better performance, it is almost always recommended that you use set-based operations in SQL
queries instead of T-SQL cursors.

Cursor Recommendations
An ineffective use of cursors can degrade the application performance by introducing extra network round-trips and
load on server resources. To keep the cursor cost low, try to follow these recommendations:

• Use set-based SQL statements over T-SQL cursors, since SQL Server is designed to work with

sets of data.

• Use the least expensive cursor.

• When using SQL Server cursors, use the

FAST FORWARD cursor type .
When using the API cursors implemented by ADO, OLEDB, or ODBC, use the default •

cursor type, which is generally referred to as the default result set.

• When using ADO.NET, use the

DataReader object.
Minimize impact on server resources. •

• Use a client-side cursor for API cursors.

• Do not perform actions on the underlying tables through the cursor.

• Always deallocate the cursor as soon as possible. This helps free resources,

especially in tempdb .
Redesign the cursor's •

SELECT statement (or the application) to return the minimum
set of rows and columns.