Kbase P10006: What is a block cursor in MS SQL server dataserver?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/06/2005 |
|
Status: Verified
GOAL:
What is a block cursor in MS SQL server dataserver?
GOAL:
Is there a documentation about the block cursor?
FACT(s) (Environment):
Progress 9.1D
SQL server DataServer
FIX:
Block Cursor For Microsoft SQL Server
In Progress Version 9.1D, block cursors are available as a performance enhancement. They allow blocks of records to be bound and fetched together from a single database request, thus reducing the network overhead and minimizing record copies for 4GL queries and finds, and minimizing record copies. In Progress Version 9.1D, block cursors become the default behavior replacing look-ahead cursors for NO-LOCK queries. A NO-LOCK query is one that executes with the NO-LOCK lock condition attached to its 4GL statement. DataServer connections that run at the read uncommitted isolation level will also execute queries that specify the SHARE-LOCK condition as NO-LOCK queries and thus will also utilize block cursors for their result sets. You can shut off block cursor behavior and revert back to look-ahead cursors for NO-LOCK queries by setting the new -Dsrv parameter PRGRS_BLOCK_CURS to zero. If you leave block cursors on but wish to turn off the block and/or look-ahead cursor optimizations for a specific query,
you can set the QUERY -TUNING option QUERY-TUNING(NO-LOOKAHEAD) on your 4GL statement. While block cursors increase efficiency, they might also require more memory than look-ahead cursors. Block cursors will try to reuse memory from previous result sets whenever possible. However, you can adjust the memory available to block cursors according to your specifications. Adjust the cache size of an individual block with the same connection-level parameter you used to set the look-ahead cache size. The QT_CACHE_SIZE -Dsrv option allows you to specify at connect time how large the block cache should be for individual query results. When block cursors are active, the default QT_CACHE_SIZE is set to 10,000 bytes (when look-ahead cursors are active, this size defaults to 30,000 bytes instead).
You can override the connection-level QT_CACHE_SIZE at the statement level by setting the QUERY-TUNING option called CACHE-SIZE on the query itself. Determining the proper block size should be based on the maximum length of your returned records multiplied by the expected number of resultant rows and then compared to your available memory. Whenever an existing block is not reused, the block of memory allocated for the query will be adjusted downward to an exact multiple of the number of rows that can fit into the allocated area. To prevent large applications from potentially over-utilizing memory for block cursors, two other settings can be adjusted to limit the amount of memory available to block cursors. The first is the maximum block memory usage per table. If you have multiple queries open against the same table simultaneously, each query uses its own query block. The PRGRS_TABLE_BLOCKSIZE -Dsrv option puts an upper limit on the total memory available to query blocks of an individual table. This number should normally be set larger than, and perhaps as a multiple of, the QT_CACHE_SIZE value. If the maximum block memory available to the table is, or would be, exceeded by allocating space for the current NO-LOCK query in your 4GL, the query is instead executed with a look-ahead cursor. The default maximum block memory area per table is set at 65,000 bytes.
The second adjustment switch available is the PRGRS_MAX_BLOCKSIZE -Dsrv option. This value sets an upper limit to the overall memory provided to block cursors (irrespective of per table allocations). The default maximum is 1048576 bytes (or 1MB). Your accumulated total memory allocated for block cursors will not exceed this value. If allocating lock cursor memory for a NO-LOCK query in your 4GL would cause this limit to be exceeded, the query would instead be executed with a look-ahead cursor. The PRGRS_MAX_BLOCKSIZE value should be set with respect to the amount of memory available on your client machine when running the Personal edition of the Microsoft SQL Server DataServer. If you are running in Enterprise mode u.sing the Enterprise edition, the number should be set with respect to both the available memory on the server machine as well as the number of Progress clients that will be connecting through your Progress database.
The following example uses the state table in the demo database, which has a maximum record size of 84 bytes:
FOR EACH state NO-LOCK QUERY-TUNING(CACHE-SIZE 850):
DISPLAY state.
END.
In this example, the QUERY-TUNING CACHE-SIZE value overrides the -Dsrv QT_CACHE_SIZE query block size default of 10,000 bytes. Ten records at 84 bytes per record would total 840 bytes in the query block. If a previously established block was located for that table that was greater than or equal to the requested size, it would be reused. However, if a new block were established instead, its memory allocation would be adjusted downward to 840 bytes in order to fit an exact multiple of rows in the block. If the result set contained 30 records, for example, the query block would get refilled three times before the end of the result set if read sequentially..