Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Initial Programming Considerations : Cursors : Block cursors : Block cursors on versus off
 
Block cursors on versus off
Block cursors are the default over lookahead cursors when PRGRS_BLOCK_CURS is enabled and a server-side cursor is needed. The switch -Dsrv PRGRS_BLOCK_CURS,0 will turn off block cursors in MS SQL Server if you want to turn off the feature for this connection. You can always disable block cursors for a specific query by disabling lookahead with a query tuning option: QUERY-TUNING(NO-LOOKAHEAD). Block cursors is not a one size fits all feature. There are application environments where the default values for block allocation sizes can result in either under-utilization or over-utilization of memory. See Using the block cursor switches for information on tuning the amount of memory that block cursors utilizes.
For any given query, the use of a block cursor or a lookahead cursor is mutually exclusive. In certain circumstances, block cursors will downgrade to lookahead. These are the criteria:
*When -Dsrv BINDING,0 is set. When binding is off, block cursors are disabled.
*Predictable single record result sets—FOR FIRST/FOR LAST or dynamic find operations.
*The PRGRS_TABLE_BLOCKSIZE has been exceeded and all existing block table space is in-use by open cursors.
*The accumulation of memory allocated to all block cursors has reached PRGRS_MAX_BLOCKSIZE.
*The QT_CACHE_SIZE query-tuning (CACHE-SIZE) value is not larger than 2 times the maximum row size. At least 2 rows need to fit in the result block to use a block cursor.