Kbase P123407: 4GL Performance: A FOR EACH query that sums up a field is very slow.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/04/2007 |
|
Status: Unverified
SYMPTOM(s):
4GL Performance: A FOR EACH query that sums up a field is very slow.
The code looks like:
FOR EACH SomeTableName NO-LOCK WHERE SomeIntegerField = SomeIntegerVariale:
dTotal = dTotal + SomeIntegerField,
END.
MESSAGE dTotal
VIEW-AS ALERT-BOX INFO BUTTONS OK.
The field SomeIntegerField is a component of several indexes but is not the leading component of any index.
CAUSE:
Because the field is not the leading component of any index, a whole table scan is performed by the query. Hence the slow performance.
FIX:
Create a new index where the SomeIntegerField is the leading component. Or, if possible, modify the WHERE clause to contain the leading components of an existing index up to and including the SomeIntegerField field.
For example, if a multiple component index has SomeLeadingField followed by the SomeIntegerField field then modifying the WHERE clause by adding the SomeLeadingField to it will allow the query to use that index and improve its performance. For example, the above query can be modified as follows:
FOR EACH SomeTableName NO-LOCK WHERE
SomeLeadingField = SomeOtherVariable AND
SomeIntegerField = SomeIntegerVariale:
dTotal = dTotal + SomeIntegerField,
END.
MESSAGE dTotal
VIEW-AS ALERT-BOX INFO BUTTONS OK.