Many uses of temporary tables are simple and brief and require little optimization. However, if your applications require multiple accesses to tables in a temporary database, examine them for possible optimization strategies. Usually, this involves splitting out the creation and indexing of the table from the access to it by using more than one procedure or batch.
When you create a table in the same stored procedure or batch where it is used, the query optimizer cannot determine how large the table is because the table was not created when the query was optimized, as shown in Figure 6-3. This applies to both temporary tables and regular user tables.
Figure 6-3: Optimizing and creating temporary tables
The optimizer assumes that any such table has 10 data pages and 100 rows. If the table is really large, this assumption can lead the optimizer to choose a suboptimal query plan.
These two techniques can improve the optimization of temporary tables:
Creating indexes on temporary tables
Breaking complex use of temporary tables into multiple batches or procedures to provide information for the optimizer
You can define indexes on temporary tables. In many cases, these indexes can improve the performance of queries that use temporary databases. The optimizer uses these indexes just like indexes on ordinary user tables. The only requirements are:
The table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics.
The index must exist while the query using it is optimized. You cannot create an index and then use it in a query in the same batch or procedure. The query processor uses indexes created in a stored procedure in queries that are run inside the stored procedure.
The optimizer may choose a suboptimal plan if rows have been added or deleted since the index was created or since update statistics was run.
Providing an index for the optimizer can greatly increase performance, especially in complex procedures that create temporary tables and then perform numerous operations on them.
You need to take an extra step to create the procedures described above. You cannot create base_proc until select_proc exists, and you cannot create select_proc until the temporary table exists.
Create the temporary table outside the procedure. It can be empty; it just must exist and have columns that are compatible with select_proc:
select * into #huge_result from ... where 1 = 2
Create the procedure select_proc, as shown above.
Drop #huge_result.
Create the procedure base_proc.
For example, this query causes optimization problems with #huge_result:
create proc base_proc
as
select *
into #huge_result
from ...
select *
from tab,
#huge_result where ...
You can achieve better performance by using two procedures. When the base_proc procedure calls the select_proc procedure, the optimizer can determine the size of the table:
create proc select_proc
as
select *
from tab, #huge_result where ...
create proc base_proc
as
select *
into #huge_result
from ...
exec select_proc
If the processing for #huge_result requires multiple accesses, joins, or other processes (such as looping with while), creating an index on #huge_result may improve performance. Create the index in base_proc so that it is available when select_proc is optimized.
