Progress
Version 9
Product Update Bulletin
Firehose and Fast Forward-Only Cursors
Firehose cursors deliver a streamlined, unmanaged, client-side cursor-processing mechanism for handling result sets from a DataServer query. When connection pooling is enabled, firehose cursors are the default mechanism for handling read-only results. If a firehose cursor is denied to an application, the DataServer first attempts to substitute a Fast Forward-Only (FFO) server-side cursor with Auto-Fetch and Auto-Close attributes in its place. If the query cannot be handled by a FFO cursor, the cursor is further downgraded.
Firehose Cursors
Firehose cursors are referred to in Microsoft SQL Server as the default result set. A default result set is generated when the statement attributes of a cursor are left unchanged from their defaults. The default result set allows rows from a query result to be pulled without locks in forward-only sequence into a client-side cache. The default result set is referred to as a firehose cursor because it can "flood" the client with results. It is unencumbered by the cursor management necessary with server-side cursors.
The following DataServer operations are eligible for the firehose cursor implementation:
- All NO-LOCK queries.
- All SHARE-LOCK queries with transaction isolation level set to read-uncommitted.
- Internal no-lock queries that populate the key cache for transaction-oriented operations.
- All stored procedure result sets.
- All SEND-SQL-STATEMENT result sets.
- Queries written with the
QUERY-TUNING(SEPARATE-CONNECTION)
keyword. When connection pooling is enabled, theQUERY-TUNING(SEPARATE-CONNECTION)
is redundant.NOTE: Prepared statements associated with firehose cursors are now cached on a statement cache that is associated with the managed connection. Statement reuse may decrease based on the recycling of managed connections. To completely disable the prepared statement cache, use the following connection switch: "-Dsrv PRGRS_PREPCACHE,0
". For information on monitoring the statement cache reuse, see the "Cursor and Connection Use Monitoring."Certain statements and fields automatically exclude a query from consideration for a firehose cursor. Two examples are:
FOR FIRST
andFOR LAST
statements. Because these statements return at most one row, they are optimized with the TSQL keywordTOP
, which renders them ineligible for a firehose cursor.- Queries that include
text
orimage
fields that translate to SQLLONGVARCHAR
andLONGVARBINARY
data types. If possible, use field lists to exclude these fields from your result set.Fast Forward-Only Cursors
In the event that a firehose cursor cannot be used, the DataServer attempts to use a Fast Forward-Only (FFO) cursor with Auto-Fetch and Auto-Close attributes. FFO cursors are the server-side equivalent of firehose cursors. FFO cursors have special optimization characteristics that distinguish them from other server-side cursors. They require a minimum of server-side resources and are capable of minimizing round trips to the server. FFO cursors are an extension to the ODBC specification and are unique to ODBC drivers that conform to Microsoft SQL Server driver requirements. The Auto-Fetch attribute directs the server to return the initial block of results in the same network message that provided the SQL request to be executed by the server. The Auto-close attribute directs the server to automatically close the cursor on the same round trip in which the last query result is received by the client.
NOTE: Result sets that includetext
orimage
columns cause an implicit conversion from an FFO to a dynamic cursor type. These are columns that translate through ODBC to SQLLONGVARCHAR
andLONGVARBINARY
data types.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |