Database Reference
In-Depth Information
On the downside, the static cursor has the following cost overhead:
•
Higher open cost than other cursor types:
The cursor open operation of the static cursor is
slower than that of other cursor types, since all the rows of the result set have to be retrieved
from the underlying tables and the snapshot has to be created in the
tempdb
database during
the cursor open.
•
Higher impact on
tempdb
than other cursor types:
There can be significant impact on server
resources for creating, populating, and cleaning up the snapshot in the
tempdb
database.
Keyset-Driven Cursors
These are the cost benefits of keyset-driven cursors:
•
Lower open cost than the static cursor:
Since only the keyset, not the complete snapshot, is
created in the
tempdb
database, the keyset-driven cursor opens faster than the static cursor.
SQL Server populates the keyset of a large keyset-driven cursor asynchronously, which
shortens the time between when the cursor is opened and when the first cursor row is fetched.
•
Lower impact on
tempdb
than that with the static cursor:
Because the keyset-driven cursor is
smaller, it uses less space in
tempdb
.
The cost overhead of keyset-driven cursors is as follows:
•
Higher open cost than forward-only and dynamic cursors:
Populating the keyset in the
tempdb
database makes the cursor open operation of the keyset-driven cursor costlier than that of
forward-only (with the exceptions mentioned earlier) and dynamic cursors.
•
Higher fetch cost than other cursor types:
For every cursor row fetch, the key in the keyset has
to be accessed first, and then the corresponding underlying row in the user database can be
accessed. Accessing both the
tempdb
and the user database for every cursor row fetch makes
the fetch operation costlier than that of other cursor types.
•
Higher impact on
tempdb
than forward-only and dynamic cursors:
Creating, populating, and
cleaning up the keyset in
tempdb
impacts server resources.
•
Higher lock overhead and blocking than the static cursor:
Since row fetch from the cursor
retrieves rows from the underlying table, it acquires an (S) lock on the underlying row (unless
the
NOLOCK
locking hint is used) during the row fetch operation.
Dynamic Cursor
The dynamic cursor has the following cost benefits:
•
Lower open cost than static and keyset-driven cursors:
Since the cursor is opened directly on
the underlying rows without copying anything to the
tempdb
database, the dynamic cursor
opens faster than the static and keyset-driven cursors.
•
Lower impact on
tempdb
than static and keyset-driven cursors:
Since nothing is copied into
tempdb
, the dynamic cursor places far less strain on
tempdb
than the other cursor types.
The dynamic cursor has the following cost overhead:
•
Higher lock overhead and blocking than the static cursor:
Every cursor row fetch in a dynamic
cursor requeries the underlying tables involved in the
SELECT
statement of the cursor. The
dynamic fetches are generally expensive because the original select condition might have to be
reexecuted.
