Fast Forward-only Cursors (Database Engine)
Microsoft SQL Server implements a performance optimization called a fast forward-only cursor. Fast forward-only cursors are supported in the following environments:
Transact-SQL cursors can specify the FAST_FORWARD clause on DECLARE CURSOR. This opens an optimized forward-only, read-only cursor.
Applications using the Microsoft SQL Server Native Client OLE DB provider can set the rowset properties DBPROP_SERVERCURSOR, DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, and DBPROP_OWNUPDATEDELETE to VARIANT_TRUE. In addition, applications should set properties that allow backward fetching or scrolling, or that allow rowset updates, to VARIANT_FALSE. These are DBPROP_CANHOLDROWS, DBPROP_IROWSETSCROLL, DBPROP_CANSCROLLBACKWARDS, DBPROP_CANFETCHBACKWARDS, DBPROP_IROWSETCHANGE, and DBPROP_IROWSETUPDATE.
Applications using the Microsoft SQL Server Native Client ODBC driver can set the driver-specific statement attribute SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_FFO or SQL_CO_FFO_AF. Setting SQL_CO_FFO requests that the cursor be opened with the same optimizations as the FAST_FORWARD clause on DECLARE CURSOR. SQL_CO_FFO_AF request that an autofetch option also be enabled.
Note
Fast forward-only cursors never use dynamic plans with spatial indexes.
Using the Autofetch Option
Although some performance improvements are realized by specifying FAST_FORWARD on DECLARE CURSOR, or by specifying SQL_CO_FFO in ODBC applications, the most important performance gain comes from specifying SQL_CO_FFO_AF in ODBC applications to enable the autofetch option. Autofetch enables two optimizations that can significantly reduce network traffic:
When the cursor is opened, the first row or batch of rows is automatically fetched from the cursor. This saves having to send a fetch request across the network.
When a fetch hits the end of the cursor, the cursor is automatically closed. This saves having to send a separate close request across the network.
The most dramatic improvement is seen when processing cursors with relatively small result sets that can be cached in the memory of an application. The fast forward-only cursor with autofetch enabled represents the most efficient method of getting a result set into an ODBC application. When the autofetch option is on for a cursor containing n rows, an ODBC application can:
Specify a rowset size of n+1.
Allocate arrays of n+1 variables to hold the data from the result set columns.
Bind the result set columns to the arrays.
Execute the SQL statement that generates the cursor.
When the ODBC driver executes the statement, it requests that the cursor be opened. Because autofetch is enabled, the server fetches and sends back n rows. The server fits as many rows as possible into each network packet returned to the client. When the server attempts to fetch the row at n+1 it detects the end of the cursor and automatically closes the cursor. When the application then executes SQLCloseCursor or SQLFreeStmt the ODBC driver does not have to send any close request to the server. The entire operation is done with only one packet being sent from the client to the server, and a minimal number of packets being returned from the server to the client.
Note
Fast forward-only cursors are never converted.