Many uses of temporary tables are simple and brief and require little optimization. But if your applications require multiple accesses to tables in tempdb, you should 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. 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 optimizer may choose a suboptimal plan if rows have been added or deleted since the index was created or since update statistics was run.
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, the table has not yet been created when the query is optimized, as shown in Figure 12-3. This applies to both temporary tables and regular user tables.
Figure 12-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
