Performance Improvement Expectation

This tip covers the expectation that Quest SQL Optimizer will always be able to improve the performance of your SQL statements.

Occasionally, even when you have increased the intelligence level up to the highest level, you may find that none of the alternative SQL statements that Quest SQL Optimizer generated improves the performance of your original SQL statement and you may wonder why. There are many reasons you cannot improve your SQL statement. For example, take a simple SQL statement; it may not have many alternative execution plans that can be explored, so you cannot improve something that is already the best. For complex SQL statements, it is normal that a lot of alternative execution plans can be generated by SQL Optimizer. But if you still cannot find a better alternative from all those SQL alternatives, it could be that your original SQL statement is already using the best execution plan to retrieve the specific data you are looking for.

In this case, what Quest SQL Optimizer has done for you is to confirm that you have the best execution plan to obtain a specific result set. Many DBAs and developers know the techniques of writing SQL statements and tuning them for good performance and have spent many hours on this process. If you have already written the best alternative, the optimization process in Quest SQL Optimizer confirms that you have the best performing SQL. Therefore you will know that you do not need to spend any more time trying to figure out if there is a better way to rewrite it.

If you still need performance improvement for a SQL statement, you may want to look into using the index generation feature in the Optimize SQL module to see if there are indexes that would produce other execution plans for your original SQL statement. Don’t forget to re-optimize your SQL statement again once you have created the new index because sometimes you can obtain even better performance based on the index.

One thing you should remember is that a new index for a given SQL statement may affect other SQL statements that are accessing the same table. You can use our Analyze Impact module to check those mission critical SQL statements that are accessing the new index table.