USE-INDEX shouldn't be used all the time. If that's the case you better add better indexes. Progress index use is very clear and there are lots of articles written about.
By using use-index you force progress to use that index even though there can be or will be in the future a better index to use.
If you use use-index only to sort then the by option is better and faster.
For instance:
In the sports 2000 database:
you want to know all customers from salesrep 'BBB' and you want to sort them by name. You can do this like you say:
Code:
for each customer where cusotmer.SalesRep = 'BBB' USE-INDEX name:
display customer.name.
end.
in this example the xref shows:
SEARCH sports2000.Customer Name WHOLE-INDEX
So the index name is used, but as whole-index which means
that progress is going to pass every record in the customer table to see if the salesrep = 'BBB'. Since he is going to use index name the sorting will be good.
If you do:
Code:
for each customer where customer.salesrep = 'BBB' by name:
display customer.
end.
In this example the xref shows:
SEARCH sports2000.Customer SalesRep
SORT-ACCESS sports2000.Customer Name
This means that the index Salesrep is used and after retrieval of the record the resultlist is sorted by name.
Now imagine you have 1 million records in the customer table from which 10000 belong to salerep 'BBB'.
In the first example every record of that 1 million is examined to see if the salerep is 'BBB'.
In the second example only the 'BBB' records are accessed and retrieved to the resultset, which after that is being sorted.
You can imagine the difference in performance between both methods....
Regards,
Casper.