Index hints in Progress ?
- Thread starter Mark123
- Start date
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:
in this example the xref shows:
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:
In this example the xref shows:
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.
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.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.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.
I use the 'Use-Index' not for sorting but for retrieving a record from a huge set of records.
If you are using it for sorting, use the 'by' or let the widget itself do the sorting. If you are retrieving a records from a table that has 10k entries then you might want to use the 'use-index' based on what results you want.
If you are using it for sorting, use the 'by' or let the widget itself do the sorting. If you are retrieving a records from a table that has 10k entries then you might want to use the 'use-index' based on what results you want.
tamhas
ProgressTalk.com Sponsor
Don't you think it is a little rude to point people to another, directly competing forum? It is one thing to point to a specific post or downloadable entry which addresses a specific question, but another to just generally suggest that someone go elsewhere on general principle. And don't you think it is a little silly when that forum has so little content? What the Progress community needs is not more forums, but better penetration into the developer community by the forums that already exist.
Well I still don't agree with that
In my opinion you should only use use-index if you have some insight in the distribution of data and you have something like "equal" indexes and one just fits better based on distrubution of data or some other valid reason.
Again performance impact can be HUGE if you use a where clause which doesn't fit your index. 1 table with 10K entries wouldn't be so much a problem but if it is common practice to use use-index then 1000 queries using this would make an impact.
Another disdavantage of using use-index is that maybe in the future better indexes will be made on that table, if that's the case you have to remove the use-index in the code. And I'll bet those use-index aren't that good documented ;-)
Regards,
Casper.
tamhas
ProgressTalk.com Sponsor
There is no question that using indexes well is key to efficient retrieval, at least when it is possible to use an index. But, using indexes and USE-INDEX are two different animals. Progress has a well documented set of rules by which it will choose an index for any particular query. Those rules are pretty sensible and will almost always make the right choice from what is available without you specifying the USE-INDEX. You might think that you as the knowledgeable programmer can help out by specifying the index, but there are a couple of very good reasons to keep your hands off. For one thing, unless you have a great system for monitoring changes to your system, later changes to the index structure may alter that best choice ... are you going to notice? But, more seriously, if you specify an index then Progress will use it .. and only it, but in any of the vaguely modern versions there is the potential for using multiple indices to resolve a query. There is no way that you can specify that in your USE-INDEX clause.
So, bottom line, put in the WHERE clause what you need, include a BY clause to specify order, use COMPILE XREF to verify the index choice, and *only* if there are special circumstances which cause you to know that the default choice is wrong, only then specify USE-INDEX.
So, bottom line, put in the WHERE clause what you need, include a BY clause to specify order, use COMPILE XREF to verify the index choice, and *only* if there are special circumstances which cause you to know that the default choice is wrong, only then specify USE-INDEX.
I would like to specify my point of view...
In my company, the use of "FOR EACH table WHERE condition" for retrieving rows is forbidden, because this instruction does not allow a very sharp management of transactions...
Instead, we use :
Thus we have to use the "USE-INDEX" clause for both FIND to sort the retrieved rows, as the "BY" keyword is reserved to "FOR EACH"...
In my company, the use of "FOR EACH table WHERE condition" for retrieving rows is forbidden, because this instruction does not allow a very sharp management of transactions...
Instead, we use :
Code:
FIND FIRST table WHERE condition
DO WHILE AVAILABLE table :
...
FIND NEXT table WHERE CONDITION.
END.Thus we have to use the "USE-INDEX" clause for both FIND to sort the retrieved rows, as the "BY" keyword is reserved to "FOR EACH"...
tamhas
ProgressTalk.com Sponsor
Try using a query instead.
And, there is nothing wrong with FOR EACH for managing transactions. Not only does the transaction automatically scope to the block, but if you want the transaction smaller than that, make the FOR EACH NO-LOCK and then have a small transaction block inside the FOR EACH which refinds the record with a different buffer as EXCLUSIVE-LOCK.
And, there is nothing wrong with FOR EACH for managing transactions. Not only does the transaction automatically scope to the block, but if you want the transaction smaller than that, make the FOR EACH NO-LOCK and then have a small transaction block inside the FOR EACH which refinds the record with a different buffer as EXCLUSIVE-LOCK.
TomBascom
Curmudgeon
Just an observation but if this sample code is any indication your company has serious problems with the understanding of record and lock scope that have little, if anything, to do with using, or not using, FOR EACH
KnutHandsome
Member
It was quite crafty. I googled first, because I was suspicious, but not sure.
Paulomongo
New Member
Hi,
I am trying to run an ODBC query against a Progress 9.1D database and want to identify way to use index's or speed up my queries.
Can I force the below query to use an index if there is one available.
SELECT
"debt1"."dt-clsearch",
"debt1"."client-code",
"debt1"."debt-code"
FROM
"PUB"."debt" "debt1"
WHERE
"debt1"."dt-clsearch"='HBOS' AND
("debt1"."dt-datinstr">={d '2006-10-02'} AND "debt1"."dt-datinstr"<={d '2006-10-30'})
I am trying to run an ODBC query against a Progress 9.1D database and want to identify way to use index's or speed up my queries.
Can I force the below query to use an index if there is one available.
SELECT
"debt1"."dt-clsearch",
"debt1"."client-code",
"debt1"."debt-code"
FROM
"PUB"."debt" "debt1"
WHERE
"debt1"."dt-clsearch"='HBOS' AND
("debt1"."dt-datinstr">={d '2006-10-02'} AND "debt1"."dt-datinstr"<={d '2006-10-30'})
RealHeavyDude
Well-Known Member
The most obvious recommendation is to upgrade to a more recent OpenEdge 10 release if you want to speed up SQL92 performance. Apart from that it is mandatory to run update statistics against any Progress database that is queried via the SQL engine on a regular base. You should check whether this is done.
Heavy Regards, RealHeavyDude.
Heavy Regards, RealHeavyDude.
Similar threads
- Locked
- Locked
- Locked
