Kbase P145706: Query slow after adding new index
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
6/16/2009 |
|
Status: Verified
SYMPTOM(s):
Query slow after adding new index
CAN-FIND uses the wrong index after adding an additional index.
XREF listing shows that the index chosen causes too many records to be read (i. e. more than was necessary for the index that was used before).
Other queries are also slower after adding the new index.
FACT(s) (Environment):
The new index is chosen instead of the index that was used before.
The index that was used before comes after the new index alphabetically.
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CHANGE:
New index added to table
CAUSE:
For the query in question, ABL applied the following rules to select the index:
A FIND operation uses a single index.
The index with the greatest number of active equality matches is preferred. In this case, the old index and the new index had the same number of active equality matches.
The first index alphabetically is preferred. In this case, the new index was chosen.
However, in this case the new index returned many more records to be evaluated than the old one had, so query performance was actually poorer even though the new index was preferable according to the ABL selection rules. This can happen because ABL is not able to predict the number of records that will be retrieved by a query.
FIX:
Consider the distribution of data when designing queries. If the index chosen by ABL will return more records to be evaluated by the query than some other index, use the USE-INDEX keyword to force the more efficient index to be used.