Kbase P156352: Query takes much longer not to find a record than to find one
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/23/2009 |
|
Status: Unverified
SYMPTOM(s):
Query takes much longer not to find a record than to find one
Unexpected amount disk activity is noticed when the query does not return a record.
FACT(s) (Environment):
Prepared query is used.
MAX-ROWS = 1 is specified.
Query string is of the form:
FOR EACH Customer NO-LOCK
WHERE Customer.SalesRep = 'RDR'
AND Customer.PostalCode = '03301'
AND Customer.City = 'Concord'
,
EACH Order OF Customer NO-LOCK
,
EACH OrderLine OF Order
WHERE OrderLine.Qty = 36
AND OrderLine.Discount = 25
AND OrderLine.OrderLineStatus = 'Shipped'
:
The largest set of records retrieved is from the second table ("Order" in the above example; note that this is unlike the actual distribution of records in the Sports2000 database).
Client-server connection to the database.
All Supported Operating Systems
Progress/OpenEdge Product Family
CAUSE:
The join between the second and third tables ("OrderLine of Order" in the above example) is from the larger table to the smaller one. When the first satisfactory record is found, "MAX-ROWS = 1" directs ABL not to continue joining the tables, and the query can return its results. If no record is found, ABL must look for "OrderLine" records that can be joined to each and every "Order" record before determining that there are none. If the first qualifying record is found fairly early in the process, the difference is noticeable in the large database. Because joins are performed on the client, disk activity on the client machine is detectable by the user when the query is slow to return.
FIX:
This is expected behavior. The developer has optimized the query for the case when a record is returned by using "MAX-ROWS = 1", but there is no equivalent optimization for the case where no record is returned.