Use-index

[Joel W Winston]

'use-index' has a direct impact on the where clause.

But does use-index have a say in the order of records in the 'for each' statement?

Does specifying the 'by' clause affect the performance?

Thanks
Joel

 

[Cringer]

Records always appear in index order unless you specify a by clause. Use index will have an impact if you are forcing Progress to use an index that isn't the one you specify. Use-index should only be used if you have a valid reason for doing so. It's not good practise. Progress is usually very good at picking indexes. In fact it is probably better at it than you will ever be.
As for using by, yes it will have an impact on performance.

 

[TomBascom]

BY will only impact performance if the BY clause specifies an order that will require sorting other than the sort order that the selection indexes provide. BY might influence index selection in that it is a potential tie-breaker between indexes that are otherwise equally able to satisfy the WHERE clause.

 

[TomBascom]

IMHO 9999 times out of a thousand USE-INDEX is a mistake.

 

[Cringer]

IMHO USE-INDEX is a mistake in 99 out of 10 cases.

 

[Joel W Winston]

Thanks for all your replies.
Joel

 

[Rob Fitzpatrick]

And yet, not everyone feels that way. I know of developers who get rapped on the knuckles if they don't use use-index. That doesn't mean I'm advocating its use. I'm just relating what I have seen in my shop.

And if you ask me whether the knuckle-rappers can prove that use-index is generally better than the alternative, I believe the answer is "no".

 

[tamhas]

It seems to me that it is a question of both context and the experience of the developer. If the developer knows the database well and thinks about the specifics of the query he or she is writing, then one would expect him or her to pick the best index. But, chances are that Progress would have picked the same index without any direction. If indexes change over time, it can also be the case that yesterday's best index is no longer today's best index and letting Progress make the choice will automate the change which might be difficult to locate otherwise. And, of course, letting Progress make the choice means that it might use multiple indices, which one can't do with use-index. Progress is certainly going to do better than a developer who doesn't understand the data structure very well, but then choosing the wrong index may be the least of the errors. To craft the right query, it seems that one needs to know what indexes are available.

 

[TomBascom]

And yet, not everyone feels that way. I know of developers who get rapped on the knuckles if they don't use use-index. That doesn't mean I'm advocating its use. I'm just relating what I have seen in my shop.

And if you ask me whether the knuckle-rappers can prove that use-index is generally better than the alternative, I believe the answer is "no".

I know of places like that too. So far nobody has managed to convince me that they are anywhere near as good as the compiler.

 

[tamhas]

Of course, the bottom line in many cases is simply that the query is poorly specified and nothing the compiler will do is going to make that better. A classic is those databases which have some kind of company code as the leading index component on many tables. Forgetting to include that company code in a query because "we only have one company here so it isn't important" will result in a full table scan no matter what the developer or compiler does!