MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
FAQ  
Scripts  
Tips  
Application  
Backup/Restore  
Bulk Copy  
Cursors  
DTS  
Indexes  
File/Filegroups  
Full-Text  
Server settings  
Db settings  
WinNT tips  
Tables design  
T-SQL  
Miscellaneous  
Replication  
Stored proc  
SQL 2000  
SQL 6.5  
OLAP  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

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.


  • *****

 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc