|
|
| |
Cursor Optimization Tips
*****
- Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can results in some performance degradation in comparison
with select statements. Try to use correlated subquery or derived tables, if
you need to perform row-by-row operations.
*****
- Do not forget to close SQL Server cursor when its result set is not needed.
To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command
releases the cursor result set and frees any cursor locks held on the rows on
which the cursor is positioned.
*****
- Do not forget to deallocate SQL Server cursor when the data structures
comprising the cursor are not needed.
To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command.
This command removes a cursor reference and releases the data structures comprising
the cursor.
*****
- Try to reduce the number of records to process in the cursor.
To reduce the cursor result set, use the WHERE clause in the cursor's
select statement. It can increase cursor performance and reduce SQL Server
overhead.
*****
- Try to reduce the number of columns to process in the cursor.
Include in the cursor's select statement only necessary columns. It will
reduce the cursor result set. So, the cursor will use fewer resources.
It can increase cursor performance and reduce SQL Server overhead.
*****
- Use READ ONLY cursors, whenever possible, instead of updatable cursors.
Because using cursors can reduce concurrency and lead to unnecessary locking,
try to use READ ONLY cursors, if you do not need to update cursor result set.
*****
- Try avoid using insensitive, static and keyset cursors, whenever possible.
These types of cursor produce the largest amount of overhead on SQL Server,
because they cause a temporary table to be created in TEMPDB, which results
in some performance degradation.
*****
- Use FAST_FORWARD cursors, whenever possible.
The FAST_FORWARD cursors produce the least amount of overhead on SQL Server,
because there are read-only cursors and can only be scrolled from the first
to the last row. Use FAST_FORWARD cursor if you do not need to update cursor
result set and the FETCH NEXT will be the only used fetch option.
*****
- Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT
will be the only used fetch option.
If you need read-only cursor and the FETCH NEXT will be the only used fetch
option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor.
By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the
other cannot be specified.
*****
|
|
|
