Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
Troubleshooting : ODBC options : Using the block cursor switches : Impact of block cursors on cache size: the -Dsrv QT_CACHE_SIZE,nnn option
Impact of block cursors on cache size: the -Dsrv QT_CACHE_SIZE,nnn option
The -Dsrv QT_CACHE_SIZE,nnn where nnn is the size in bytes of the cache to be allocated for a cursor's result set. This is a connection level default that can be overridden at the query level. If the connection level cache size is not overridden at the query level, then the query cache size times the number of open cursors cannot exceed the maximum block areas for the table set by the -Dsrv PRGRS_TABLE_BLOCKSIZE switch at any given time. The accumulation of query cache sizes from each block cursor that has been allocated cannot exceed the total maximum block areas available to block cursors as set by the -Dsrv PRGRS_MAX_BLOCKSIZE switch. If either block cursor limit is reached, cursors will downgrade to lookahead cursoring. There is no minimum for this value, however if two or more records cannot be returned to the established block, a lookahead cursor is used. The query tuning cache size should be set higher than the maximum record size times two to prevent cursor downgrades.
ODBC_DEF_BLKCACHESZ is the default value for QT_CACHE_SIZE when block cursors are enabled. This value is currently set to 10,000 bytes.
ODBC_DEF_LHDCACHESZ is the default value of QT_CACHE_SIZE when lookahead cursors are enabled. It is currently set at 30,000 bytes.
The value of QT_CACHE_SIZE represents an upper limit for the row space available to the lookahead cache, not the amount of space a lookahead cache will actually use. This highlights a key distinction between block and lookahead cursors. The "cache size" for block cursors is preallocated before results are retrieved so this value represents an actual allocated amount. For lookahead cursors, memory is accrued as rows are read back from the result set and added to the cache. So the "cache size" specifies an upper limit on the number of cached rows allowable, not an exact amount of space that will be allocated as is the case with block cursors.