How to identify missing indexes? How to optimize procedure?

R

raptor

New Member
Hello

There's a procedure, which replaces all user-IDs (f.e. user1 to user2). This procedure is very old and the dev is not available anymore.

Now, I should make this procedure much faster. I did some changes, but I think that I have to add some more indexes now. I know which tables need much time, but how to identify which indexes I have to add? Is there something to do in the code or just add an index and recompile the procedure?

Sorry for the stupid questions and thank you for your help!


(Version: Progress 9.1d)
 
You won't need to make any changes to the code if the index you add is the correct one. You'll just need to recompile.

As for deciding which indexes to use, that's a much more tricky notion. Maybe if you post the queries in use here people might be able to give some advice.
 
You can always compile an ABL source code with the LISTING option. This will produce a listing with which you can diagnose insufficient index uses. Look at the SEARCH reference type and check for WHOLE-INDEX. In case you see that it means that you are table scanning.
[QOUTE]WHOLE-INDEX means that the selection criteria specified to search the table does not offer opportunities to use indexes that allow optimized key references (bracketed high and low values). Instead, the AVM must search the entire table using available indexes (often only the primary index) to satisfy the query, hence a WHOLE-INDEX search. Thus, depending on the query, you might be able to optimize the search by adding indexes.[/QUOTE]

Heavy Regards, RealHeavyDude.
 
I don't know when exactly it was introduced - but I am 100% positive that it was introduced before V9 reared it's ugly head.

I am saying this because 9.1d is Stone Age software which is not supported anymore for years.

Heavy Regards, RealHeavyDude.
 
FYI the option you need is XREF, not LISTING. It will not tell you which indexes you need to add, it will just help you identify which queries are inefficient.
 
Cringer, you are completely right - of course it is the XREF option. Should have read my response twice before pressing the submit button ...

Heavy Regards, RealHeavyDude.
 
XREF has been around for a very long time.

WHOLE-INDEX isn't perfect. It is definitely a good indicator but there are times when a table scan is exactly what you want, so WHOLE-INDEX is a false positive in those cases. There are also times when a bracket *is* being used (thus no WHOLE-INDEX) but the query is still not as efficient as it could be, those cases are false negatives (for the use of WHOLE-INDEX as a query efficiency indicator).

Furthermore -- an inefficient query that is only very rarely used, and/or which only operates on a fairly small set of data may not be worth bothering with. Remember Amdahl's Law:

The performance enhancement possible with a given improvement is limited by the fraction of the execution time that the improved feature is used.
Click to expand...
This means that you should also be looking at the run-time table and index utilization -- you may be very surprised at what your application really spends its time on. I suggest ProTop as an excellent tool to support that process ;)

Having said all of that -- XREF isn't perfect but it is a very good start.
 
Hello again

Sorry for the late answer. I had a lot of other things to do. I added many indexes for tests but the procedure takes nearly the same time like before. In the code temp-tables and buffers are used very often. Are indexes used in temp-tables and buffers?
 
Buffers use the same indexes as the tables they refer to. They are just different names for the same thing. Temp tables use indexes - and it's worth adding them - but you won't see a massive hit as they reside in memory anyway and are therefore very quick to access.
 
Cringer said:
Buffers use the same indexes as the tables they refer to. They are just different names for the same thing. Temp tables use indexes - and it's worth adding them - but you won't see a massive hit as they reside in memory anyway and are therefore very quick to access.
Click to expand...

Assuming they don't grow to the point where they are written out to disk. I've seen some dumb things done with temp-tables.
 
You must log in or register to reply here.
Back
Top