When you copy a SQL statement from the SQL Scanner to the SQL Optimizer module, the Send to SQL Optimizerfunction automates this process for you. It copies the SQL statement, opens the SQL Optimizer window and inserts the SQL statement in the SQL Editor pane. When the SQL statement you are copying also includes a temporary table, the Send to SQL Optimizer function also copies the SQL statements for creating and populating the temporary table to the User-Defined Temp Table module.
Two items need to be considered when the SQL statements are automatically copied in the User-Defined Temp Table module. Is there SQL text already in the module? And does the temporary table already exist with the same or different data?
If SQL text is already entered into the User-Defined Temp Table module, you are prompted to save the current SQL to an ASCII file before it is overlaid with the new text. Saving the text enables you to use it again.
If a table with the same name already exists and you would like to recreate it with either a new definition or different data, then you must drop the table before executing the new SQL text. You can drop temporary table from the Temp Table List tab in the User-Defined Temp Table module.
One of the techniques for improving the performance of a SQL statement is to create a temporary table. When the SQL Optimizer rewrites the original SQL statement, some of the alternatives may involve this technique.
Here is an illustration of the original SQL and one of the SQL alternatives.
SELECT *
FROM A
WHERE A.KEY IN (SELECT B.KEY
FROM B)
SELECT DISTINCT COL1 = B.KEY INTO #TEMP1 FROM B
SELECT *
FROM A, #TEMP1
WHERE A.KEY = #TEMP1.COL1
DROP TABLE #TEMP1
Note: Under the Optimization tab in the Preferences, the section on Temp Table Generation enables you to specify whether to allow the generation of temporary tables during the optimization process.
Under the Preferences window, the SQL Scanner and Optimization tabs contain several settings that control the use of temp tables in the SQL Scanner and SQL Optimizer modules.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
