Kbase P133978: Poor performance from 4GL / ABL FOR EACH query WHERE index value is greater than zero OR less than z
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/11/2008 |
|
Status: Unverified
SYMPTOM(s):
Poor performance from 4GL / ABL FOR EACH query WHERE index value is greater than zero OR less than zero
4GL / ABL FOR EACH query WHERE index value is greater than zero OR less than zero is slow
When the program is compiled with the XREF option, the SEARCH line in the listing shows that the index is being searched WHOLE-INDEX
FACT(s) (Environment):
USE-INDEX is specified
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CAUSE:
The AVM cannot choose only a subset of records to examine (called "bracketing" the index) because two separate ranges are specified. An index scan is performed over the entire table using the index specified.
FIX:
If the logic of the application allows, one of the following options will execute faster:
Break the query into two separate queries, one for records where the index value is greater than zero and one for records where the index value is less than zero.
If few zero values are expected on the index, do not exclude the zero-valued records in the query; check in the application code for zero values as each record is processed, and skip over the zero-valued records.