Use Temporary Tables
Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.
Original SQL
SELECT *
FROM A
WHERE A.KEY IN (SELECT B.KEY
FROM B)
SQL Alternative
SELECT DISTINCT COL1 = B.KEY
INTO #TEMP1
FROM B
SELECT *
FROM A, #TEMP1
WHERE A.KEY = #TEMP1.COL1
Notes:
-
Since temporary tables are session related, you need to create temporary tables for each new session.
-
SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.
Scan Source Code with Temp Tables
About Temporary Tables
Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.
Original SQL
SELECT *
FROM A
WHERE A.KEY IN (SELECT B.KEY
FROM B)
SQL Alternative
SELECT DISTINCT COL1 = B.KEY
INTO #TEMP1
FROM B
SELECT *
FROM A, #TEMP1
WHERE A.KEY = #TEMP1.COL1
Notes:
-
Since temporary tables are session related, you need to create temporary tables for each new session.
-
SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.
Scan Source Code with Temp Tables
Create Temporary Tables
You can use the Temp Table Manager window to create temporary tables in SQL Optimizer or SQL Scanner. The Temp Table Manger supports the following SQL statements that create or modify temporary tables:
- SELECT INTO
- CREATE TABLE
- CREATE INDEX
- DROP INDEX
- DROP TABLE
- INSERT
- UPDATE
- DELETE
SQL Optimizer or SQL Scanner automatically drops the temporary tables you create using the Temp Table Manager when you quit the program or select a new connection.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To create a temporary table
-
Click
in the Alternative Details pane of SQL Optimizer or the Job List pane of SQL Scanner.
-
Review the following for additional information:
Temp Table Manager Description SQL Script Editor Enter SQL statements to create temporary tables.
Tip: Click
to open a file with your SQL statement.
SQL Script Displays the SQL script for temporary table you select. Temp Table List Displays a list of temporary tables for your current session.
Tip:Click
to drop all temporary tables for your current session.
Note: If the selected SQL statement uses a variable, the Set Bind Variables window displays so you can define the variable. See Set Bind Variables for more information.
Use Temporary Tables in Scan SQL
Optimize Indexes
Optimize Indexes analyzes a SQL workload or any group of SQL statements and determines the best index set for the workload or group of statements. You can instruct SQL Optimizer to gather SQL from an application workload during a specified time period. SQL Optimizer evaluates your SQL workload and provides you with the best set of indexes to optimize database performance for that workload. After this evaluation process is complete, you can review the results and then select the new indexes you want to save and test.
You can instruct SQL to gather a SQL workload from any of the following sources:
- Scan Code — Scan SQL statements from file, database object source, or clipboard.
- Plan Cache — Collect SQL from the Plan Cache
- SQL Profiler — Collect SQL from a trace table or trace file.
- Spotlight Statistics Repository — Collect SQL from Spotlight Statistics Repository.
After collecting the statements, SQL Optimizer provides you with the execution plans and run-time statistics. Select the statements for which you want to optimize indexes. After running the evaluation process, review the results to determine the performance improvements.
Considerations
Things to consider when using Optimize Indexes:
- When analyzing workloads, SQL Optimizer uses proprietary, modified genetic algorithms (patent pending) to continuously improve its results. Therefore, the longer the evaluation process runs, the better the result.
- While performing a search for new indexes to optimize performance, SQL Optimizer updates its recommendations over time to reflect any changes in data distribution or configuration.
- You can schedule the evaluation process to run once, continuously, or periodically at defined intervals.
- You can stop the process manually or configure it to stop automatically when specific goals are met.
- The evaluation process can be paused and resumed.
- Optimize Indexes' evaluation of SQL and search for new indexes is a low-overhead process.
- Select, update, delete, insert, and merge statements are supported.
- Only one index search process can be active at any one time. However, multiple SQL workloads can be opened and reviewed concurrently.
About the Evaluation Process
When evaluating your SQL workload, SQL Optimizer creates virtual indexes. No indexes are created, so this process does not significantly affect database performance.
Note: To optimize indexes for a single SQL statement, use Optimize SQL. See About Optimizing SQL (SQL Rewrite) for more information.
Optimize Indexes for Spotlight Statistics Repository Workload