Kbase P132904: 4GL/ABL: Getting poor performance running a FOR EACH query using the NOT operator in a WHERE clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
02/07/2009 |
|
Status: Verified
SYMPTOM(s):
4GL/ABL: Getting poor performance running a FOR EACH query using the NOT operator in a WHERE clause
A WHOLE-INDEX scan results from using the NOT operator in the WHERE clause of a FOR EACH query
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Versions
OpenEdge Category: Language (4GL/ABL)
CAUSE:
Index selection within the 4GL is based on what satisfies a query, not on what does not satisfy a query.
FIX:
The use of the NOT operator in the WHERE clause requires each record to be evaluated. It is better to write conditions of the where clause which do not require the NOT operator.
For example, the following is an example of a poor performing query because it results in a WHOLE-INDEX scan:
FOR EACH customer NO-LOCK WHERE NOT custnum = 500.
XREF output for the above "bad" query example:
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 COMPILE C:\OpenEdge\WRK101C\p132904A.p
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 CPINTERNAL ISO8859-1
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 CPSTREAM ISO8859-1
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 ACCESS sports2000.Customer CustNum
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 SEARCH sports2000.Customer CustNum WHOLE-INDEX
C:\OpenEdge\WRK101C\p132904A.p C:\OpenEdge\WRK101C\p132904A.p 1 STRING "CustNum" 7 NONE UNTRANSLATABLE
The following is an example of how the above poor performing query can be optimized to use brackets and avoid the cose of a WHOLE-INDEX scan:
FOR EACH customer NO-LOCK WHERE custnum GT 500 OR custnum LT 500.
XREF output for the above "good" query example:
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 COMPILE C:\OpenEdge\WRK101C\p132904B.p
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 CPINTERNAL ISO8859-1
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 CPSTREAM ISO8859-1
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 ACCESS sports2000.Customer CustNum
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 ACCESS sports2000.Customer CustNum
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 SEARCH sports2000.Customer CustNum
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 SEARCH sports2000.Customer CustNum
C:\OpenEdge\WRK101C\p132904B.p C:\OpenEdge\WRK101C\p132904B.p 1 STRING "CustNum" 7 NONE UNTRANSLATABLE
In the XREF output for the bad query example a WHOLE-INDEX search is being performed which is equivalent to a full table scan.
In the XREF output for the good query the custnum index is used to identify only those records WHERE custnum > 500 OR custnum < 500.
Note that two searches are performed using the index CustNum. This is a visible example of multi-bracket searching where the ranges of the where clause use the same index in conjunction to isolate the requested records.