Kbase P15773: Is it possible not to use a word index bracket first in a CONTAINS query resolution.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
07/05/2010 |
|
Status: Verified
GOAL:
4GL/ABL: Is it possible NOT to start with a word index bracket in the resolution of a CONTAINS operator query.
GOAL:
Does the resolution of a 4GL/ABL query involving the CONTAINS operator ALWAYS start by using the word index first?
GOAL:
Why does a query like the following has a slow performance when executed against a large table?
FOR EACH Customer WHERE
Customer.CustNum = 52 AND
Customer.Comments CONTAINS "Customer" NO-LOCK:
DISPLAY Customer.CustNum Customer.Name.
END.
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
No, it is NOT possible NOT to start with a word index bracket in the resolution of a CONTAINS operator query. A Progress 4GL/ABL query involving the CONTAINS operator will ALWAYS be resolved using the word index first.
In the example above, the larger bracket based on the word index "Comments" is generated before the smaller bracket based on the non-word index "CustNum" and is matched against the second bracket. This explains the slow performance of this query.
To determine whether it is better to resolve the query by suppressing the use of the word index, bench mark your query using several code variations similar to the following snippets against a realistic representation of your data to determine the optimum query performance:
1. Use the MATCHES operator:
FOR EACH customer WHERE CustNum = 52 NO-LOCK:
IF comments MATCHES "*customer*" THEN DISPLAY custnum NAME.
END.
2. Use the INDEX function:
FOR EACH customer WHERE CustNum = 52 NO-LOCK:
IF INDEX(comments, "customer") > 0 THEN DISPLAY custnum NAME.
END.
3. Use a Temp-Table:
DEFINE TEMP-TABLE CustTable NO-UNDO
FIELD CustNum LIKE Customer.CustNum
FIELD CustName LIKE Customer.NAME
FIELD CustComments LIKE Customer.Comments
INDEX index-name IS WORD-INDEX CustComments.
FOR EACH customer WHERE
CustNum = 52 NO-LOCK:
CREATE CustTable.
ASSIGN
CustTable.CustNum = Customer.CustNum
CustTable.CustName = Customer.NAME
CustTable.CustComments = Customer.Comments.
END.
FOR EACH CustTable:
DISPLAY CustTable.CustNum CustTable.CustName.
END.