Good management of temporary databases is critical to the overall performance of Adaptive Server. However, temporary tables can add to the size requirement of tempdb. Using temporary tables greatly affects the performance of Adaptive Server and your applications. You cannot overlook the management of temporary databases or leave them in a default state. On many servers, tempdb is the most dynamic database.
You can avoid most of the performance issues with temporary databases by planning in advance, and taking these issues into consideration:
Temporary databases fill frequently, generating error messages to users, who must then resubmit their queries when space becomes available.
Temporary databases sort slowly and queries against them display uneven performance.
User queries are often temporarily blocked from creating temporary tables because of locks on system tables.
Heavily used objects in a temporary database flush other pages out of the data cache.
Resolve these issues by:
Configuring a sufficient number of user temporary databases.
Sizing temporary databases correctly for all Adaptive Server activity
Placing temporary databases optimally to minimize contention
Minimizing the resource locking within temporary databases
Binding temporary databases to their own data cache
Configuring temporary database groups correctly
Binding logins and applications to the appropriate temporary database or group.
