Hello,
 Any idea on how to detect unoptimized queries globally ?

Thank you

Charalampos

> Any idea on how to detect unoptimized queries globally ?

1. Have a look in the EDBManager code and see how Tim builds the execution plan code.

2. A fair bit of programming.

a) get a list of indices and their first field

select * from information.indexcolumns where ordinalpos = 0

b) analyse the query for indices needed - any where, group by, order by, join clause (don't forget to do the same for sub-selects)

c) compare the two and list any columns that don't have an equivalent index.

This assumes that your queries are Stored Procedures or in some other easily get-at-able way.

3. Subclass the EDBQuery component so that it always generates and stores an execution plan and then write some code to analyse that. Since it comes in a standardised format that shouldn't be to difficult. There is a problem in that it only really works for fairly simple queries. When you add in sub-selects its not so good.

This approach does have a number of benefits:

a) if you store each run of the query you can see how popular that query is and how much effort should go into optimising it rather than telling the user to have a cuppa

b) you can adjust the database by adding indices pretty much on the fly (ok you do have to make sure others are logged off)

c) you can analyse results and see if things have improved

d) you can give special attention to the complex queries and do some simulations

Roy

Thank you Roy!

Charalampos


f you decide to go ahead I'd be intyerested in which approach you take.

Roy Lambert

Michael,

<< Any idea on how to detect unoptimized queries globally ? >>

Besides Roy's recommendations, you can also use the ENABLE STATEMENT LOGGING statement to have ElevateDB log any queries that take longer than N seconds to execute.

You can then query this table to see what shows up:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=LoggedStatements_Table

This system information table is in the Configuration database.

This can also be helpful with queries that, because they are lock or I/O-heavy, perform well during development, but end up hitting concurrency performance issues during actual deployment.

Tim Young
Elevate Software
www.elevatesoft.com