Allocate sufficient space to temporary databases to handle the following processes for every concurrent Adaptive Server user:
Worktables for merge joins
Worktables that are created for distinct, group by, and order by, for reformatting, and for the or strategy, and for materializing some views and subqueries
Hashed temporary tables (those created with “#” as the first character of their names)
Indexes on temporary tables
Regular user tables in temporary databases
Procedures built by dynamic SQL
Some applications may perform better if you use temporary tables to split up multitable joins. This strategy is often used for:
Cases where the optimizer does not choose a good query plan for a query that joins more than four tables
Queries that join a very large number of tables
Very complex queries
Applications that need to filter data as an intermediate step
You might also use temporary databases to:
Denormalize several tables into a few temporary tables
Normalize a denormalized table to do aggregate processing
Determine the sizes of temporary databases based on usage scenarios. For most applications, make temporary databases 20 – 25% of the size of your user databases to provide enough space for these uses.
