SQL Tuner automatically checks SQL statements for basic Informix specific SQL design "rules-of-thumb" violations defined by Informix experts and generates alternative SQL that corrects them. It can execute multiple versions of SQL statement and presents graphical explain plans and execution statistics for easy comparison. You can also manually tune SQL using convenient user interface that allows side-by-side comparison of original and tuned SQL statements, which includes source code, produced execution plans, execution results and statistics. SQL Tuner can be invoked on user-provided SQL statements, as well as on SQL statements automatically collected on Informix back-end by Sentinel SQL Capture utility.
Integrated Index Adviser performs a thorough examination of specified SQL statements and presents index recommendations based on tables which are used by these statements. Optimizing your indexes is useful for situations in which you may be unable to change the SQL statements being issued, but can change the indexing schema on the database tables.
There are two ways to open SQL Tuner:
In opened SQL Editor component, select SELECT statement that you want to tune and choose Tune SQL from popup menu.
Choose Tools -> SQL Manager -> SQL Tuner top-level menu. Connect to a database in which you want to tune SQL statement. Type, paste or open from file SELECT statement that you want to tune.
SQL Tuner window consists of Tuner Analysis panel on top and SQL Editor panel at bottom.
Tuner Analysis area contains results of automatic analysis of SQL statement. Analysis panel includes two parts: code inspection and index recommendations. Code Inspection provides suggestions on how to rewrite SELECT statement. If there is any suggestions that can be automatically implemented, there is Apply Suggestion button under each suggestion. The modified SELECT statement is generated in the right editor of the SQL Editor area.
Index Recommendation part provides suggestions which indexes can be created for tables referred by SELECT statement that might improve performance without rewriting SQL code. If there is an index recommendation, a text field is displayed that contains CREATE INDEX SQL statement. You can copy this statement using Ctrl+C and paste it into separate SQL editor to execute.
SQL Editor panel consists of one or two SQL editors positioned side-by-side for easy comparison of two versions of SQL SELECT statements.
When SQL Tuner window is opened first time, there is only one original version of SELECT statement and you can see only one SQL Editor document in the bottom area. Once you start modifying SQL SELECT statement by either applying suggestions generated by Tuner Analysis or manually, the second editor is opened at the right side of the first one that contains the original SQL SELECT statement. You can compare side-by-side SQL source code, execution plans produced by both statements or results of execution of both statements.
Each SQL Editor have the following three small buttons above text editing area: Analyze SQL Query, Get Execution Plan, Execute.
To create a new version of SQL SELECT statement you have to perform one of the following operations in SQL editor:
You can see all versions of SQL SELECT statement generated in both left and
right editors by using Tuner History tab located under each SQL Editor area. Any
version of SQL statement can be made active and placed into any editor for
further modifications. To place SQL version from Tuner History to SQL
editor, select the required version in the grid and choose Tune Selected Version
from the right-mouse-button popup menu.
Execution Plan operation displays graphical execution plan diagram as well as shows estimated cost produced by Informix optimizer. You can compare side-by-side plans produced by two versions of SQL statements and their estimated costs. In many cases, the statement that has better estimated cost has also a better performance. But to make sure that the performance is actually better or worth, you need to actually execute the statement to get the execution time.
When you execute SQL SELECT statement in any editor, you can specify number of times that the statement has to be executed. The default is to execute SELECT statement 1 time. It might be needed if you want to get an average time that takes to execute this statement (each execution time might be different based on a current Informix server load). Results of execution that includes number of returned rows, average execution time as well as resulting data records are displayed under text editor area. You can compare side-by-side results and time statistics produced by two versions of SELECT statement to choose best performing statement as well as to verify that both versions return the same data set.