|
Free Resources from FMS |
|
|
|
|
"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - Darren D. |
|
|
|
|
Microsoft
Access Resource Center
Avoid Using Cursors
Provided by: Molly Pell, Systems Analyst and Total SQL Analyzer PRO
Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements. A cursor can contain one, some, or all records from an executed SQL statement.
Cursors generally use a lot of resources and reduce the performance and scalability of applications. If you need to perform row-by-row operations, try to find another method to perform the task. For example, consider the following options:
-
Perform the task at the client
-
Use tempdb tables
-
Use derived tables
-
Use a correlated sub-query
-
Use the CASE statement
More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor.
If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.
If you are unable to use a fast-forward cursor, then try the following cursors, in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset
