Use-index
- Thread starter SSuhaib
- Start date
TomBascom
Curmudgeon
The alternative is to let the compiler choose indexes. It is almost always going to do a better job than you are. Personally I have never found a case where USE-INDEX was helpful in a schema that I designed. I have only very rarely been persuaded that it might be helpful in schemas that somebody else designed.
Progress 4GL uses a static, rule based query optimizer. The SQL-92 engine uses a cost based optimizer. You should only ever override the optimizers if you have solid evidence that you know something special about your data distribution that makes you smarter than the optimizer.
If the special thing that you know is that some fields in the query aren't part of an index or that the query breaks the bracketing rules then you should very strongly consider adding an appropriate index or refactoring the query.
If you still believe that you are smarter than the compiler you should prove that you are smarter by running tests and documenting your results so that the next person to come along can confirm that the circumstances that made your decision are still valid and either continue with your override or eliminate it if things have changed.
(If you are optimizing SQL-92 queries make sure to run UPDATE STATISTICS first.)
Progress 4GL uses a static, rule based query optimizer. The SQL-92 engine uses a cost based optimizer. You should only ever override the optimizers if you have solid evidence that you know something special about your data distribution that makes you smarter than the optimizer.
If the special thing that you know is that some fields in the query aren't part of an index or that the query breaks the bracketing rules then you should very strongly consider adding an appropriate index or refactoring the query.
If you still believe that you are smarter than the compiler you should prove that you are smarter by running tests and documenting your results so that the next person to come along can confirm that the circumstances that made your decision are still valid and either continue with your override or eliminate it if things have changed.
(If you are optimizing SQL-92 queries make sure to run UPDATE STATISTICS first.)
AndrewSmith
Member
As Tom said, in an ideal world there shouldn't be a need to use USE-INDEX. However, in the real world we sometimes have to work with badly-designed database schemas which use many crazy indexes, and we can't change them for one reason or another.
In those cases, the use of USE-INDEX is pretty much unavoidable. Sometimes, the index selected by the compiler just isn't the one you want, and you just have to specify it. If you have to do that in the minority of cases, then congratulations! You have a well-designed schema!
In those cases, the use of USE-INDEX is pretty much unavoidable. Sometimes, the index selected by the compiler just isn't the one you want, and you just have to specify it. If you have to do that in the minority of cases, then congratulations! You have a well-designed schema!
erickroco9611
New Member
Hi... I'd like to know what´s happen when I use USE-INDEX but a don´t complete all the fields that are required (or especified) in the Index definition.
For example...
if I run
for each po USE-INDEX po-index
where po.cd-whs = 10
and po.estatus = 2
no-lock:
... procedure ...
end.
But the index is defined:
INDEX po-index
FIELD cd-whs
FIELD cd-dep
FIELD estatus
The USE-INDEX Keyword is going to affect in bad form my query?
Thanks.
For example...
if I run
for each po USE-INDEX po-index
where po.cd-whs = 10
and po.estatus = 2
no-lock:
... procedure ...
end.
But the index is defined:
INDEX po-index
FIELD cd-whs
FIELD cd-dep
FIELD estatus
The USE-INDEX Keyword is going to affect in bad form my query?
Thanks.
tamhas
ProgressTalk.com Sponsor
It can only bracket on what you give it and does so in the order of the index. So, that query will read every record where cd-whs = 10. It will also lock every record until it finds one where estatus = 2. So, you can tell based on the nature of the data if that is a lot of excess records to read or a lot to lock.
You can get around the locking problem by reading no-lock, which you are doing here anyway, and then when you need to modify, reread the record into another buffer.
You can get around the locking problem by reading no-lock, which you are doing here anyway, and then when you need to modify, reread the record into another buffer.
TomBascom
Curmudgeon
You are forcing the selection of the po-index index. Why? When did you suddenly become smarter than the compiler? Can you prove that you are smarter than the compiler?
If po-index is the only index on the table it will be used anyway.
If po-index is the only index that has cd-whs as its leading component it will be used anyway (because you have an equality match on cd-whs).
If there are multiple indexes that have cd-whs as a leading component and if one or more of those indexes has estatus as the second component then forcing of po-index has seriously hurt performance.
If you add such an index next week the code will not use it until you remove the USE-INDEX.
If you are using po-index because you want a certain ordering of records you should be using a BY clause.
If po-index is the only index on the table it will be used anyway.
If po-index is the only index that has cd-whs as its leading component it will be used anyway (because you have an equality match on cd-whs).
If there are multiple indexes that have cd-whs as a leading component and if one or more of those indexes has estatus as the second component then forcing of po-index has seriously hurt performance.
If you add such an index next week the code will not use it until you remove the USE-INDEX.
If you are using po-index because you want a certain ordering of records you should be using a BY clause.
Similar threads
- Locked