We are converting from Progress 9.1.E to Open Edge 10.1.C. The following code takes about 47 minutes to run in 9.1.E but almost 2.5 hours in 10.1.C.
FOR EACH account NO-LOCK, FIRST customer WHERE customer.customer# = account.customer# NO-LOCK BY customer.last-name BY customer.first-name WITH FRAME x
I have determined that the problem is the BY statements. Removing the BY statements makes the 10.1.C run in about 24 minutes or less. The 9.1.E and 10.1.C versions were both compiled and the XREF listings for this code are identical. Both specify SORT-ACCESS for the BY statements and the same indexes for the FOR EACH and FIRST statements. An index exists for the customer WHERE statement, customer.customer# = account.customer#. An index also exists for the BY statements, customer.last-name and customer.first-name. The data was loaded into 10.1.C by a Dump and Reload.
It appears that 10.1.C is not using an index to sort the returned data but 9.1.E does.
Was there a change between 9.1.E and 10.1.C in how the index selection for the BY statements works?
The Open Edge 10.1.C ABL Triggers and Indexes manual states, on page 12 - Case 1,
"Sample Where Clause: WHERE Customer.Name Begins "B"
If there is a BY field clause and field is indexed, ABL uses the index to sort returned records as long as there is no index on the WHERE clause. If field is not indexed, ABL creates a temporary sort table and sorts the records at run time."
What happens if the WHERE clause and the BY clauses are indexed?
It appears the 10.1.C is not using an index to sort the data but 9.1.E did?. Is this correct?
If this is not an index issue, what else could be causing this?
As a code work around, we are changing this code and other similar code to use temp tables. The BY statements are removed, the data written to a temp table which has the BY fields indexed. This option works but we would prefer not to have to make this change throughout our system.
Thanks for all assistance.