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.
WHERE A.KEY IN (SELECT B.KEY
SELECT DISTINCT COL1 = B.KEY INTO #TEMP1 FROM B
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.
Throughout the program, SQL statements are saved in the SQL History so that you can use them again. They are stored in a file so that they are always available to work with again.