Kbase P105452: How does qt_cache_size work?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/12/2008 |
|
Status: Verified
GOAL:
How does qt_cache_size work?
GOAL:
What is the qt_cache_size parameter?
GOAL:
How does cache size influence block and lookahead cursors?
FACT(s) (Environment):
Progress 9.1E
OpenEdge 10.x
MS SQL DataServer
Windows
FIX:
The lookahead cache is used to store query results. Its use over time has morphed with the introduction of new cursor types. In the past, lookahead cursors provided only minimal performance gains because it still only retrieved records from the server one by one. More recently block cursors were introduced which use the cache_size value to determine how big a block of memory should be allocated to download server results to the client. There are a number of startup parameters that can adjust the cache size per table and/or per session when using block cursors but the cache_size still determines the size of the result set per query.
When firehose cursors were introduced in 9.1E and 10.0B, the cache size for these cursors determined how big a results cache would be used to receive the entire result set (as opposed to blocks of records or single records). If the cache isn't big enough to store the whole result set, then the connection is blocked until all the results are read. This is not a problem for the 4GL session because multiple connections are used but it does mean that resources are held longer than they would need to be if the cache was big enough to store the whole result set.
In all the above cases, sizing the cache size large enough to handle as much of a result set as possible while not making it so large that memory is wasted because of over-allocation is the real trick to fine tuning the application. Fortunately, if you have an idea how big a result set is, you can override the cache size specified at start up with a query-tuning setting on the query itself. You shouldn't try to predict result sizes. It is better to think about allocating bigger caches for bigger result sets and smaller caches for smaller results.
But no matter what the cache size is, the DataServer will pick the most efficient cursor available to satisfy the query requirements and then use the look-ahead cache to cache results when available, regardless of size (as long as its large enough for one result row). The closer the cache is sized to the size of the result set the better. But, just having it on (which is the default) is most important to performance. If you do not have lookahead cursors on then the lookahead, block and firehose cursors are all unavailable. When this happens then the old Progress-style record retrieval methods are used and slow server side cursors process the request. This method is very compatible with legacy Progress applications but will negatively affect performance.
One key difference between block cursors versus firehose and lookahead cursors is that block cursors pre-allocate a cache based on the size of a table record so they are tied to a specific table structure whereas firehose and old-style cursors allocate their cache dynamically. This means block cursors assume the result set is retrieved from a single table and must be disabled for join conditions. Whereas firehose cursors can handle any result set or any query request independent of what table's are involved.
There really are no Progress-compatibility issues using lookahead cursors. A cursor type is always matched to the situation to maintain Progress compatibility. The only compatibility issue is that with a lookahead cursor the result set is defined at the time that the query is executed. Therefore, if a record changes in the result set before the time the user cursors to that record in the result set, the user will not see those changes reflected in the returned rows. Using a cursor of the slower "compatible" type would enable records modified by other users to be read by the user as the cursor was moved through the result set. This is "compatible" with Progress behavior.