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.
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 18-3. This applies to both temporary tables and regular user tables.
Figure 18-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
