Database Reference
In-Depth Information
•
Avoid T-SQL cursors entirely by rewriting the logic of the cursor as set-based statements,
which are generally more efficient than cursors.
•
Use a
ROWVERSION
column for dynamic cursors to benefit from the efficient, version-based
concurrency control instead of relying upon the value-based technique.
•
Minimize impact on
tempdb
.
Minimize resource contention in
•
tempdb
by avoiding the static and keyset-driven cursor types.
Static and key-set cursors put additional load on
•
tempdb
, so take that into account if you must
use them, or avoid them if your
tempdb
is under stress.
Minimize blocking.
•
•
Use the default result set, fast-forward-only cursor, or static cursor.
•
Process all cursor rows as quickly as possible.
•
Avoid scroll locks or pessimistic locking.
•
Minimize network round-trips while using API cursors.
•
Use the
CacheSize
property of ADO to fetch multiple rows in one round-trip.
Use client-side cursors.
•
•
Use disconnected record sets.
Summary
As you learned in this chapter, a cursor is the natural extension to the result set returned by SQL Server, enabling the
calling application to process one row of data at a time. Cursors add a cost overhead to application performance and
impact the server resources.
You should always be looking for ways to avoid cursors. Set-based solutions work better in almost all cases.
However, if a cursor operation is mandated, then choose the best combination of cursor location, concurrency, type,
and cache size characteristics to minimize the cost overhead of the cursor.
In the next chapter, we explore the special functionality introduced with in-memory tables, natively compiled
procedures, and the other aspects of Hekaton in SQL Server 2014.
