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 whether reading the whole table leads to performance issues depends on the actual amount of data in the table.
In addition, absence of a WHOLE-INDEX tag does not always mean a query won't read more records than it needs to.
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. Normally a TEMP-TABLE will only contain a limited subset of data, and the number of records will not be large enough to form a bottleneck. 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 even if a bracket can be established and no WHOLE-INDEX tag is reported. This happens when the bracket actually spans 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 > " " AND Name < "~~" :
DISPLAY Customer.
END.
