Kbase 15253: 4GL/ABL: What does the XREF WHOLE-INDEX tag means?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/04/2009 |
|
Status: Verified
GOAL:
4GL/ABL: What does the XREF WHOLE-INDEX tag means?
GOAL:
Does the appearance of the WHOLE-INDEX tag in the XREF listing of a procedure necessarily mean that a performance issues exists?
FACT(s) (Environment):
All Supported Operating Systems
Progress 8.x
Progress 9.x
OpenEdge 10.x
FIX:
WHOLE-INDEX searches in the compile XREF output are often used to identify potential performance bottlenecks, but they are often misunderstood. The key point is that they indicate potential performance bottlenecks, not necessarily actual ones. Thus a WHOLE-INDEX search should not be taken as conclusive evidence of performance issues by itself.
The WHOLE-INDEX tag in the XREF listing means that the selection criteria specified to search the table does not offer opportunities to use indexes that allow optimized key references (bracketed high and low values) and therefore, Progress performs an index scan over the entire table using the specified index.
This may indicate the whole table will be read when resolving the query, but this is not always the actual result. And wether reading the whole table leads to performance issues depends on the actual amount of data in the table.
Common cases where a WHOLE-INDEX scan does not indicate a performance issue:
- A single FIND FIRST or FIND LAST on a table. While these will bracket an entire index, they will return only 1 record - the one matching either the first or the last index entry.
- Queries on TEMP-TABLEs. Often, the data in a TEMP-TABLE is a limited subset determined when the table is populated, and the code processing it does not need to limit this data further. If a query on a TEMP-TABLE takes longer to execute than expected, further investigation will be needed to ensure appropriate indexes are present, and the TEMP-TABLE does not hold more data than expected - the query itself does not have to be the root cause.
Further examples, using the Sports database:
The following queries will report a WHOLE-INDEX search there are no WHERE criteria specified to limit the number of records. The first returns the records in order of the primary index, the second in order of the Name index. The application design may require this.
FOR EACH Customer NO-LOCK:
DISPLAY Customer.
END.
FOR EACH Customer NO-LOCK USE-INDEX Name:
DISPLAY Customer.
END.
This query will also report a WHOLE-INDEX search on the primary index, and will search the entire table. This is because there is no index provided for the Balance field to limit the search. This is an indication that indexing the Balance field will improve performance.
FOR EACH Customer NO-LOCK WHERE Balance < 10000 AND Balance > 5000:
DISPLAY Customer.
END.
The next two queries do not result in WHOLE-INDEX searches because the selection criteria directly limits the range of Name and Cust-num index keys (respectively) to be searched.
FOR EACH Customer NO-LOCK WHERE Name < "Penan Sporttiklubi" AND Name > "Chip's Poker":
DISPLAY Customer.
END.
FOR EACH Customer NO-LOCK WHERE Cust-Num < 40:
DISPLAY Customer.
END.
Also note that a query may still actually read the entire table despite the fact that a bracket can be established. This because the bracket may actually span all records.
For example the following query brackets the Name index on a range of characters which include all names in the table. It will not report a WHOLE-INDEX scan:
FOR EACH Customer NO-LOCK WHERE Name > CHR(3.2) AND Name < CHR(129) :
DISPLAY Customer.
END..