You can test a single SQL statement to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).
To send SQL statement to Benchmark Factory:
Optimize the original SQL statement.
Select the original or one of the SQL alternatives
You can save the Optimized SQL statements. Unlike a report, this file is used to reload the SQL back to the SQL Optimizer window using the Open Optimized SQL function.
To save the original SQL and the SQL alternatives
Select SQL | Save Optimized SQL.
After you have saved the optimized SQL statements to a file, you can load them back through the SQL Optimizer window:
To load a saved optimized SQL statements and alternatives
Select SQL | Open Optimized SQL.
Displays the original connection and optimization settings information.
Last Saved Query plan Information
Displays the saved and current connection information and whether there are any changes in SQL structure and query plans.
If the SQL statement uses a temporary table, the User-Defined Temp Table tab displays in this window. It displays the DDL used to create the temporary table.
If there are any changes to the SQL structure or the access plans, the Changes tab displays inI this window. It displays the SQL text along with the saved and current query plan.
If there are any changes in either SQL structure or query plans it is advisable that you refresh the query plans so that the reloaded image is a truth reflection of the current environment before any further testing is done. Click Refresh Plan from the Open Optimized SQL Details window. If there are changes in the query plan the corresponding SQL statements run time and statistics information are deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate query plans.
After refresh, the Refresh Plan Details window can be displayed. This window displays the number of query plans refreshed, the total eliminated, and invalid plans.
The Open Optimized SQL Details and Refresh Plan Details windows can be reviewed at a later stage.
To view the details
Select View | Show Open Optimized SQL Details and View | Refresh Plan Details.