Usage of Index

[TomBascom]

My understanding (and perhaps mistakenly) always has been that given a "tie", OE will always use the index that returns the most records.

As Rob said -- do you mean least?

Is that true?

No.

You are describing something that sounds like a "cost based optimizer". The SQL-92 engine does that but it depends on the statistics about key distribution generated by UPDATE STATISTICS. The 4GL engine uses a "static optimizer" or "rules based optimizer" that selects the index at compile time. The 4GL is unlikely to ever adopt the cost based optimizer -- it would likely break a lot of applications.

Your "returns the most records" confusion might be a mis-remembering the rule that the 4GL tries to select the index with the most equality matches. That is one of the more important index selection rules (but there are conditions and caveats -- "most equality matches on leading components" is a better description of the rule).

 

[TomBascom]

Yes, there are shops where USE-INDEX is a way of life. That doesn't make it right. They probably have lots of other worst-practices going on too.

Whenever you think that you are smarter than the compiler you should prove it. First to yourself and then to the maintenance programmer who comes along later and will need to understand why you did what you did.

If you actually find a case where USE-INDEX provides better results you should document, in great detail, why you think so. What is your evidence? What index did the compiler choose? (Compile with XREF to find out.) Why was that index chosen? (Perhaps your WHERE clause is poorly constructed...) What tests did you run to show that your choice is better? What were the results of those tests? What data sets was used to run those tests? (Remember DEV and TEST environments are not always realistic datasets -- the maintenance programmer or bug fixer may need to know why PROD is behaving differently.)

The chances are very good that during the documentation process you will discover that what you think is happening is not really happening or is not happening for the reason that you think that it is. And that there may very well be a much better way to solve the problem.

 

[Cringer]

Do not get angry, but I would like to know, why not use USE_INDEX?. This was recommended in some manuals of progress and we have several systems using this for years.

In addition to Tom's reply above, there are a number of things in the manuals etc that are actually considered to be bad practise.

 

[Rob Fitzpatrick]

One thing in the manuals (ABL Triggers & Indexes) that every developer should read however is the index selection rules. Generally speaking, they aren't well understood.

 

[JOSE_LUIS_VEGA]

I am referring to that here we use use index, to multiple systems, in particular when fields are quite similar, with multiple indexes, i am referring to 15 or 20 indexes per table.

 

[JOSE_LUIS_VEGA]

What you say is true, for simple systems, i am talking about complex systems with many tables and indexes.

 

[JOSE_LUIS_VEGA]

You can be, anyway we have achieved an adequate performance, with access to tables with multiple fields, I am referring to: 10 fields that comprise a access, it is necessary to access the table by 8, 7 or 6 of them, and we use for that secondary indexes. Little did we realize another best way, if there is another i want to know.

 

[TomBascom]

In my humble opinion a complex system with many tables, many fields and many indexes makes it even more important to let the compiler choose. You cannot possibly have a better understanding of those matters than the compiler does.

As I said -- I know that there are companies that make it a rule to always use USE-INDEX. In my opinion they are wrong. That is a very bad idea and they are fooling themselves thinking otherwise.

The Progress compiler almost always makes the best choice. Cases where it does not are very, very rare. USE-INDEX should only ever be used in those exceedingly rare cases where you can PROVE that your choice is better than the compiler.

By all means -- compile with XREF and verify that index selection makes sense and, more importantly, that it works. I think you will be surprised at how well it takes care of itself.

Of course if your company has made it a coding rule you might be fighting a losing battle. But it never hurts to experiment. And it might come in useful if you ever go somewhere more reasonable.

(There are also companies that seriously "over think" the number of indexes that they create and cause themselves all sorts of problems that way.)

 

[Cringer]

I can only agree with Tom on this. A lot of the cases where use-index has been used (incorrectly IMO) that I have seen are either
a) because the index setup is wrong
b) because other best practises are not being followed within code

 

[JOSE_LUIS_VEGA]

Truly appreciate your knowledge and explanation, it is always good change of direction, I see that adopt this rule by inheritance from my place of work. Now I'm going to be able to do as I want, because I am as independent. I am going to put in practice this i accepted, and from already thank you very much.

 

[Cringer]

If you get stuck working out specific examples I'm sure people would be happy to cast an eye over the problem in future

 

[JOSE_LUIS_VEGA]

If you get stuck working out specific examples I'm sure people would be happy to cast an eye over the problem in future

I thank you a lot of concepts, i'm going to have it, in my country we say, a hug.

 

[RealHeavyDude]

Please excuse me stepping late - but this are just my thoughts:

As already explained there are very rare cases where the usage of use-index might be appropriate. Not because one is smarter than the compiler but to work around insufficient indexes. Many developers I know refrain from changing the database schema, instead they are coding work arounds which, in reality, makes the issue even worse. All this workarounds may bite you where it really hurts when some years later somebody decides to fix the indexes because it is the right thing to do and thereby is accidentally breaking your application.

IMHO, Progress is notorious for their bad practice in sample code. That's because this sample codes is mostly created by people that do not develop real life appliactions. This samples are just to illustrate how some ABL statements can be used but not necessarily illustrating good practice.

Furthermore, many developers I know - including myself - develop routine solutions for routine problems. This means your are repeating stuff that you know works. If you include bad practice from the beginning it will start to spread and later on nobody - including yourself - will know why and might think it is because it is a rule or considered good practice ...

Remember, one can learn something new everyday.

Me, I am just trying to highlight bad practice when I see it. Not because putting the blame on somebody else, but to assist making your code better and more future proof.

Heavy Regards, RealHeavyDude.