Kbase P147331: Multi-table dynamic ABL query generates a high number of database reads when using -rereadnolock
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/3/2009 |
|
Status: Unverified
SYMPTOM(s):
Multi-buffer dynamic ABL query generates a high number of database reads when using -rereadnolock
Multi-table dynamic ABL query generates double the expected number of database reads when using -rereadnolock
Static query against EACH Sports2000.Customer, FIRST Sports2000.Order of Sports2000.Customer with -rereadnolock returns 1117 reads
Dynamic query against EACH Sports2000.Customer, FIRST Sports2000.Order of Sports2000.Customer with -rereadnolock returns 2234 reads
FACT(s) (Environment):
All queries specify NO-LOCK
Sessions use the startup parameter -rereadnolock
Single-table dynamic query generates expected number of database reads
Equivalent static query generates expected number of database reads
Equivalent FOR EACH statement generates expected number of database reads
CACHE option is not specified on the dynamic query
Dynamic query is not associated with a BROWSE widget
Progress 9.1D
Progress 9.1E
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
This is expected behaviour. The CACHE option has not been set for the dynamic query which forces the AVM to re-read every record. From the -rereadnolock entry in the Startup Command and Parameter Reference manual:
"It [-rereadnolock] has no affect on the behavior of the query cache used for a query with NO-LOCK that is specified through the CACHE phrase of the DEFINE QUERY statement. To force the AVM to always re-read the record, set the cache size to zero (0). However, this may significantly degrade performance if the database is accessed across a network. Set the cache size to zero only when it is critical to retrieve the most current version of a record."
From the Online Help for DEFINE QUERY, the CACHE option,
"Specifies the number of records of the query to hold in memory for a NO-LOCK query. Generally, caching more records produces better browse performance when accessing a database across a network. However, caching consumes both memory and CPU time for buffer management."
FIX:
Set the CACHE option of the query to a value higher than zero if the high number of reads is not desired but the use of multi-buffer dynamic queries plus -rereadnolock is required.