Question Performance issue

[ank123]

This is related to 4GL programs. There are few programs (report and maintenance) which take very munch time to get completed. These are taking hours to be completed.

Please suggest what are best ways to find out causing this problem and how can this be fixed.
Mainly how can i check if indexing is done incorrectly.

Regadrs
Ankit

 

[Cringer]

Lots of things to consider. This is exactly the scenario I'm working on a guide for, but it's not started yet so in the meantime...
1) Compile the code with XREF. Check the indexes that are being used are sensible indexes. Also check you aren't getting WHOLE-INDEX reads where they aren't necessary.
2) Make sure -tablerangesize and -indexrangesize are set appropriately for your database. These are server startup parameters. They are documented in the documentation. Once they are sized correctly you can use promon, or better, ProTop (http://www.dbappraise.com/protop.html) to analyse the reads of a given query. You can then use this to tune the query.
3) Try not to have functions in your WHERE clause. But definitely don't have them on the left hand side eg "WHERE INT(somefield) EQ 1234".
4) Don't have lots of ORs. If your query has lots of ORs, load the values want into a temp table and join that.
5) If the programs are updating the database check your transaction scoping is nice and tight.
6) If your queries are based on user input then you may find a dynamic query is of use as you then only use the filters provided rather than all of them.

There's loads and loads in this area to consider, but that should keep you busy for a while!

 

[TheMadDBA]

What Cringer said.... Adding a few more things

Look into the _UserTableStat and _UserIndexStat VSTs. They will show what an individual session is doing.

Protop will help you see what is happening for a session or you can use the VSTs directly in your code (debug only) to trace the calls.

Also the Profiler is a great tool to use to find out exactly where the time is being spent in your programs. Unless the code is very simple (a few for each statements) I usually start with that. It will trace execution time per line and you can use the tool to activate profiling or evaluate the results. http://knowledgebase.progress.com/articles/Article/20336

 

[ank123]

What Cringer said.... Adding a few more things

Look into the _UserTableStat and _UserIndexStat VSTs. They will show what an individual session is doing.

Protop will help you see what is happening for a session or you can use the VSTs directly in your code (debug only) to trace the calls.

Also the Profiler is a great tool to use to find out exactly where the time is being spent in your programs. Unless the code is very simple (a few for each statements) I usually start with that. It will trace execution time per line and you can use the tool to activate profiling or evaluate the results. http://knowledgebase.progress.com/articles/Article/20336

 

[ank123]

Thanks for your reply.

I have never used these tools. Could you please suggest where can I read more about ProTop, VST and Prifiler to understand detailed usage of this?
Thanks again
BR

 

[TheMadDBA]

Cringer provided a link for ProTop. It is an excellent tool to find out what is going on in your DB in general and simple to install/use.

Profiler documentation will be included in the download in that KB I linked.

VSTs and other features are in the OpenEdge documentation https://community.progress.com/comm...2352.openedge-11-5-product-documentation.aspx. Or you can look at the ProTop source code to see how it shows the table activity (by db and by user).

 

[Rob Fitzpatrick]

It is also a good idea to get to know the concept of log entry types. These are additional bits of AVM (ABL Virtual Machine) logging you can enable individually, each with their own level of verbosity. The details they log go into the application's client log (or AppServer server log, or WebSpeed agent log, as appropriate).

Log entry types are documented in detail in the Debugging and Troubleshooting manual. In this case I would look at enabling the QryInfo and 4GLTrace log entry types. You will be able to see which programs and internal procedures you are running, and you will get statistics about your database queries (DB blocks accessed, records returned, index used, etc.).