Consultor Eletrônico

Status: Unverified

GOAL:

4GL/ABL: Why is my CAN-FIND function slower than my FOR FIRST statement?

GOAL:

What makes a FOR FIRST statement have a better performance than the CAN-FIND function using the same WHERE clause and where all the predicates uses non-unique indexed fields?

GOAL:

Why does the FOR FIRST statement in the following code perform better than the CAN-FIND function in the following code:

DEFINE BUFFER bufTransaction FOR devTransaction.
DEFINE VARIABLE logScrap AS LOGICAL NO-UNDO.
ETIME (YES).
FOR FIRST devTransaction NO-LOCK
WHERE devTransaction.PartCD = "2086637"
AND devTransaction.Serial = 41080
AND devTransaction.TransactionType = 'Pick'
:
FOR EACH bufTransaction NO-LOCK
WHERE bufTransaction.PartCD = devTransaction.PartCD
AND bufTransaction.Serial = devTransaction.Serial
AND bufTransaction.TransactionType = 'Scrap'
:
LEAVE.
END.
END.
MESSAGE 1 ' - ' ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.
ETIME (YES).
FOR FIRST devTransaction NO-LOCK
WHERE devTransaction.PartCD = "2086637"
AND devTransaction.Serial = 41080
AND devTransaction.TransactionType = 'Pick'
:
logScrap = CAN-FIND(FIRST bufTransaction
WHERE bufTransaction.PartCD = devTransaction.PartCD
AND bufTransaction.Serial = devTransaction.Serial
AND bufTransaction.TransactionType = 'Scrap').
END.
MESSAGE 2 ' - ' ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.


FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)

FIX:

This difference in performance is due to query optimizations that exist only for FOR statement but not for the FIND statement or CAN-FIND function.

The above code contains a special case WHERE clause where multiple non-unique equality index references are ANDed. In the case of the FOR statement, we can use multiple indexes to resolve the query, while in the CAN-FIND function , we can only use a single index. Hence the superior performance of the FOR FIRST statement over the CAN-FIND function in the above code.