Even though the trunc log on checkpoint database option is turned on in temporary databases, Adaptive Server still writes changes to temporary databases to the transaction log. You can reduce log activity in a temporary database by:
Using select into instead of create table and insert
Selecting only the columns you need into the temporary tables
When you create and populate temporary tables in a temporary database, use the select into command, rather than create table and insert...select, whenever possible. The select into/bulkcopy database option is turned on by default in temporary databases to enable this behavior.
select into operations are faster because they are only minimally logged. Only the allocation of data pages is tracked, not the actual changes for each data row. Each data insert in an insert...select query is fully logged, resulting in more overhead.
If the application creating tables in a temporary database uses only a few columns of a table, you can minimize the number and size of log records by:
Selecting only the columns you need for the application, rather than using select * in queries that insert data into the tables
Limiting the rows selected to just the rows that the applications requires
These suggestions also keep the size of the tables themselves smaller.
