Reg : Usage of USE-INDEX

saravanakumar

saravanakumar

Member
Hi Everybody,
I need a clarification, the scenario is as follows;
Consider a query has been written with an AND condition in find first. We know which INDEX progress uses for search criteria. So can we use USE-INDEX here? If we are not using USE-INDEX here is there any performance hit???

Will usage of USE-INDEX reduces any time (bcoz we know it construct a logical tree for choosing the INDEX and go through some process)?

Is it that usage of USE-INDEX in our code is good???

Can anyone help me out? Thanks in advance.:)
 
We know which INDEX progress uses for search criteria. So can we use USE-INDEX here? If we are not using USE-INDEX here is there any performance hit???
Click to expand...

I always tell my programmers to not use use-index unless it is absolutely nessecary.

snippet from our Programming standards:
Reasons not to use us-index:
  • The AVM is in certain situations capable of using more then 1 index. The use of use-index prohibits this functionality.
  • If an index is changed or a new better index for a query is added then use-index either doesn’t serve his purpose anymore or the new index will not be chosen by the AVM.
Reasons to use use-index:
  • Find first/last/previous/next may need use-index in order to get the right record.
  • If one has knowledge of the data distribution in a table and there and multiple indexes can be chosen. Some times the AVM doesn’t choose the most optimal index. In theses cases use-index can improve performance. If one uses use-index in such cases then a comment has to be made in the sources to justify the use of use-index.
Click to expand...

Casper.
 
Hi Casper,
Thanks for ur information. I need to know if we are using USE-INDEX progress wont create logical tree and those set of stuffs to choose an INDEX. So is that time not saved???

Here in this case we are not giving Progress compiler a chance to choose index, am i right? correct me if i am wrong.

Can you be bit more clear? So that i will be able to provide the same stuff to my team. Please...

Thanks in advance.
 
I need to know if we are using USE-INDEX progress wont create logical tree and those set of stuffs to choose an INDEX. So is that time not saved???
Click to expand...
There is no time saved by using use-index.

Here in this case we are not giving Progress compiler a chance to choose index, am i right? correct me if i am wrong.
Click to expand...

From the online help:
Use-index:
Identifies the index you want to use while selecting records. If you do not use this option, the AVM selects an index to use based on the criteria specified with the WHERE, USING, OF, or constant options.
Click to expand...

Casper
 
Hi Everybody,
Like to re-open the thread for a small clarification.

I like to know if USE-INDEX suppresses the default INDEX that is chosen by AVM or USE-INDEX does not allow AVM to choose the INDEX itself???

consider if it does not allow AVM to choose the INDEX then we can directly go to the XREF file and check what INDEX is being used by Progress on its own for the 1st time and provide it using USE-INDEX. So that next time AVM need not choose any index on its own. Correct me if i am wrong...
 
With use-index the AVM is going to use that index.
Like I said earlier, there is no performance gain in the way you state it by using use-index.
IMO, There are more reasons not to use use-index then to use use-index. I think I described my opinion on this in the earlier answer I gave you.

Casper.
 
USE-INDEX overrides the index selection algorithm that the compiler uses. If your code says USE-INDEX then that index will be used and it will show up in the XREF.

That can be a good thing if you know something special about the data in your database or if you need a particular ordering of your data.

However... IMHO programmers rarely know what they think they know about the data. And even if they do know something about the data the end-users may start doing things differently somewhere down the road which changes everything.

Furthermore, if ordering of your data matters then FIND is almost certainly the wrong statement to be using in the first place. If you are using USE-INDEX to specify a sort order in some statement other than FIND then you need to spend some quality time with the documentation looking at BY.

IMHO USE-INDEX is almost always a mistake. It prevents the compiler from choosing multiple indexes (where that is possible) and it enforces a particular index no matter how bad that index turns out to be.

USE-INDEX is especially bad when accompanied by comments in the code that indicate that a programmer put it there for performance purposes ;)

If you think you're smarter than the compiler prove it. Don't just blithely throw a USE-INDEX in your code. Run tests and gather data about the number of logical reads required to satisfy your query. Document the results via comments in the affected code so that when I see those comments I will be able to verify that you actually are smarter than the compiler.
 
Keep in mind that the AVM only decides what index to use at compile-time, never at run-time. With 1 exception and that is dynamic queries. So when a compiled program (.r) is run the information on what index(es) to use is already put into the code by the compiler. If you decide to run uncompiled programs then the performance gain of using USE-INDEX will be completely lost because the AVM has to compile the code first.

The use of USE-INDEX is bad practice and most of the times indicates a poorly designed database schema.
 
use-index becomes very usefull when you are using a database different from progress. we got several customers with oracle db connected via schema holder. using "use-index" boosted the performance of programms processing large tables.
 
Are you using dataserver and do you also know why this boosted performance?

I don't use dataserver but I can't imagine that it should be default pratice to use use-index in any circumstance.

Casper.
 
There is supposed to be a method of providing index hints of some sort to data servers. Oracle uses a cost based optimizer so I would not expect that forcing it to use a particular index would be a very good idea.
 
my point was that oracle in quite alot cases doesnt use the optimal index.

we dont got the best database schema. there are some tables with 10+ indexes (where only 1 field differs) and there is a lot of redundancy. with progress as database it looks like the decision of which index needs to be used for the best result always works. with oracle in contrast this only works 1 out of 10 times (i dont have the exact numbers but it's often...). quite often the primary index is chosen although the query only uses one field of this index.
i'll try to log this tomorrow at work and post some benchmarks if you want.
 
I never thought that I'd say this but...

Oracle isn't that bad.

I'd guess that you're doing something wrong with it. Like perhaps you haven't run UPDATE STATISTICS. Do you have an Oracle DBA?
 
You must log in or register to reply here.
Back
Top