SQL Server Temporary Tables Optimization Tips
Use the table variables instead of the temporary tables whenever possible. The table variable is a new SQL Server 2000 feature. The table variables are created and stored in memory while the temporary tables are created and stored in the tempdb database. So the table variables require less locking and logging resources than temporary tables and table variables should be used whenever possible. Try to avoid using temporary tables inside your stored procedure. Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.
Try avoid using insensitive, static and keyset cursors whenever possible. These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in tempdb database, which results in some performance degradation. Use multi-statement table-valued functions to eliminate temporary table usage for intermediate result processing. Because the table variables require less locking and logging resources than temporary tables, try to use multi-statement table-valued functions instead of temporary tables. Try to avoid using temporary tables by rewriting your Transact-SQL statements to use only standard queries or stored procedures.
Use the derived tables or correlated sub-queries instead of the temporary tables whenever possible. Because temporary tables are stored in the tempdb database on the hard disk, using them can increase the number of the disk read/write operations. So you should avoid using temporary tables whenever possible. Use local temporary tables instead of SQL Server cursors. Because using temporary tables usually are more efficient than using cursors, you should use temp tables instead of cursors whenever possible. Avoid creation temporary tables from within a transaction. Creation temporary tables inside a transaction requires many locking and logging resources, can lock some system tables and can decrease the overall SQL Server performance. Avoid using global temporary tables.
SQL Server supports local and global temporary tables. Both of these table types are stored in the tempdb system databases. The local temporary tables are visible only in the current session and their names prefix with single number sign (#table_name), while the global temporary tables are visible to all sessions and their names prefix with a double number sign (##table_name). Because the local temporary tables are dropped automatically at the end of the current session, while the global temporary tables are dropped automatically only when all tasks have stopped referencing them, you should avoid using global temporary tables. Consider creation a permanent table instead of using temporary tables. If your queries contain a loop and uses temporary tables again and again, consider creation a permanent table to store the intermediate results. In this case, you will save some time because you will not create table and drop table again and again. Because all temporary tables are stored in the tempdb database, consider spending some time on the tempdb database optimization. For example, place tempdb on a fast I/O subsystem to get good performance (try to use RAID 0 disks for the tempdb database). Set a reasonable size for the tempdb database and a reasonable autogrow increment. Because automatically growing results in some performance degradation, you should set a reasonable size for the tempdb database and a reasonable autogrow increment to avoid tempdb automatically growing too often.
