SQL Optimizer for SAP ASE 3.8 - Release Notes

Create Alternative SQL Which Uses Temporary Tables

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.

Original SQL

SELECT *

FROM A  

WHERE A.KEY IN (SELECT B.KEY  

FROM B)                   

SQL1 Alternative

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.

Related Topics

User-Defined Temp Table Overview

Create Temporary Tables

Parameters in Temporary Table SQL

View SQL scripts of temporary tables

Drop Temporary Tables

Example of Temporary Tables in SQL Scanner

Copy SQL with Temporary Tables to SQL Optimizer

Preference Settings for Handling Temporary Tables