Menu
What is the advantage of using FAST_FORWARD for defining a cursor? Is it better for performance? why?
269
A cursor keeps track of the position in the result set, and allows you to perform multiple operations row by row against a result set, with or without returning to the original table. In other words, cursors conceptually return a result set based on tables within the databases.
Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set.
A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.
The definition from MSDN is:
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.
I've boldened the key bit. It can support these "performance optimisations" because it does not need to support multi-direction iterating through the cursor (FORWARD_ONLY) and does not support modifications (READ_ONLY).
Of course, if you don't really need to use a cursor at all - then using a cursor even with this option is not going to perform as well . If you can do the same task using a set-based approach, do that instead - this is the bit I really wanted to stress.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
