Fast Forward-only Cursors
Microsoft® SQL Server™ 2000 implements a performance optimization called a fast forward-only cursor. Fast forward-only cursors are supported in two 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 OLE DB Provider for SQL Server can set the rowset properties DBPROP_SERVERCURSOR, DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, and DBPROP_OWNUPDATEDELETE to VARIANT_TRUE.
- Applications using the Microsoft SQL Server 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.
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 SQL Server 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.
Implicit Conversion of Fast Forward-only Cursors
Fast forward-only cursors are implicitly converted to other cursor types when:
- If the SELECT statement joins one or more tables with triggers to tables without triggers, the cursor is converted to a static cursor.
- If the SELECT statement references text, ntext, or image columns the cursor is converted to a dynamic cursor if the OLE DB Provider for SQL Server or the SQL Server ODBC driver are used.
- If a fast forward-only cursor is not read-only, it is converted to a dynamic cursor.
- If the SELECT statement is a distributed query that references one or more remote tables on linked servers, the cursor is converted to a keyset-driven cursor.
- If the SELECT statement references text, ntext, or image columns and a TOP clause, the cursor is converted to a keyset-driven cursor.