Using Temporary Tables in Stored Procedures
Temporary tables are commonly used in stored procedures when you need to store intermediate results in a work table for additional or more advanced processing. You need to keep a few things in mind when using temporary tables in stored procedures.
In versions of SQL Server prior to 7.0, if a subprocedure referenced a temporary table created externally, a temporary table with the same name and structure had to exist at the time the stored procedure was created. This is no longer the case now that SQL Server performs deferred name resolution. The existence of the temporary table is not checked until the stored procedure is executed.
Local temporary tables created in a stored procedure are automatically dropped when the stored procedure exits. Global temporary tables created in a stored procedure will still exist after the stored procedure exits until they are explicitly dropped (see Listing 28.22) or the user session in which they were created disconnects from SQL Server.
Listing 28.22 Using Local and Global Temporary Tables in Stored Procedures
create proc temp_test2 as select * into ##temp from publishers select * into #temp from publishers go exec temp_test2 go select * from ##temp go pub_id pub_name city state country ------ -------------------------- -------------- ----- ----------- 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA 1389 Algodata Infosystems Berkeley CA USA 1622 Five Lakes Publishing Chicago IL USA 1756 Ramona Publishers Dallas TX USA 9901 GGG&G München NULL Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris NULL France select * from #temp go Server: Msg 208, Level 16, State 1, Line 1 Invalid object name '#temp'.
Note what happens if you try to run the stored procedure again:
exec temp_test2 go Server: Msg 2714, Level 16, State 6, Procedure temp_test2, Line 3 There is already an object named '##temp' in the database.
TIPPersonally, I don't find much use for global temporary tables in stored procedures. The typical reason for using temporary tables in stored procedures is that you need a work area within the stored procedure only. You normally wouldn't want it sticking around after the procedure finishes. Creating a global temporary table in a stored procedure requires an explicit drop of the table before the procedure exits if you no longer need it. If that's the case, what's the benefit of using a global temporary table? Any subprocedures will be able to see and reference a local temporary table created in the calling procedure, so global temporary tables are not needed in that case. Only if you need to create and populate a worktable and have it available after the procedure exits should you consider using a global temporary table. However, you would have to remember to explicitly drop it at some point before attempting to run the procedure again. However, if an error occurs that aborts processing of the stored procedure, the explicit drop might not be executed. You might want to include a check for the global temporary table in your stored procedure and drop it automatically before attempting to create it again, as in the following code snippet:
|
'##global_temp'
and type = 'U')
drop table ##global_Temp
select * into ##global_temp from ...
Temporary Table Performance Tips
All users within SQL Server share the same tempdb database for work tables and temporary tables, regardless of the database in which they are working. This makes tempdb a potential bottleneck in any multiuser system. The primary bottleneck in tempdb is disk I/O, but locking contention can also exist between processes on the tempdb system tables.
SQL Server 2000 solves the disk I/O problem a bit by logging just enough information to allow rollback of transactions without logging all the additional information that would be necessary to recover those transactions. The recovery information is needed only when recovering a database at system startup or when restoring from a backup. Because tempdb is rebuilt during SQL Server startup (and no one in their right mind would restore tempdb from a backup), it's unnecessary to keep this recovery information. By reducing the logging in tempdb, data modification operations on tables in tempdb can be up to four times faster than the same operations in other databases.
On the other hand, locking in tempdb is still a potential performance bottleneck. If you create a table in tempdb within a transaction, locks are held on rows in the system tables sysobjects, syscolumns, and sysindexes related to the table created, as shown in Listing 28.23.
Listing 28.23 Locks Held on System Tables in tempdb When Creating a Temporary Table
begin tran
select * into #temptab from titles
exec sp_lock
go
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 2 0 0 DB [BULK-OP-LOG] NULL GRANT
51 5 0 0 DB S GRANT
51 2 0 0 DB [BULK-OP-DB] NULL GRANT
51 2 1 0 TAB IX GRANT
51 2 3 0 TAB IX GRANT
51 2 2 0 TAB IX GRANT
51 2 3 2 KEY (bd018d280de1) X GRANT
51 2 3 2 KEY (b101e50ba351) X GRANT
51 2 3 1 KEY (080056b1859e) X GRANT
51 2 3 1 KEY (0900b81e308c) X GRANT
51 2 3 1 KEY (0e008aeeeebb) X GRANT
51 2 3 1 KEY (0b0064415ba9) X GRANT
51 2 3 1 KEY (0f00dcfe8de3) X GRANT
51 2 0 0 PAG 1:78 X GRANT
51 2 0 0 PAG 1:77 X GRANT
51 2 0 0 PAG 1:94 X GRANT
51 2 0 0 PAG 1:95 X GRANT
51 2 0 0 PAG 1:92 X GRANT
51 2 0 0 PAG 1:93 X GRANT
51 2 0 0 PAG 1:90 X GRANT
51 2 0 0 PAG 1:91 X GRANT
51 2 0 0 PAG 1:89 X GRANT
51 2 0 0 EXT 1:104 X GRANT
51 2 0 0 PAG 1:102 X GRANT
51 2 0 0 PAG 1:103 X GRANT
51 2 0 0 PAG 1:100 X GRANT
51 2 0 0 PAG 1:101 X GRANT
51 2 0 0 PAG 1:98 X GRANT
51 2 0 0 PAG 1:99 X GRANT
51 2 0 0 PAG 1:96 X GRANT
51 2 0 0 PAG 1:97 X GRANT
51 2 0 0 PAG 1:110 X GRANT
51 2 0 0 EXT 1:96 X GRANT
51 2 0 0 PAG 1:111 X GRANT
51 2 0 0 PAG 1:108 X GRANT
51 2 0 0 PAG 1:109 X GRANT
51 2 0 0 PAG 1:106 X GRANT
51 2 0 0 PAG 1:107 X GRANT
51 2 0 0 PAG 1:104 X GRANT
51 2 0 0 PAG 1:105 X GRANT
51 2 0 0 PAG 1:118 X GRANT
51 2 0 0 PAG 1:119 X GRANT
51 2 0 0 PAG 1:116 X GRANT
51 2 0 0 PAG 1:117 X GRANT
51 2 0 0 PAG 1:114 X GRANT
51 2 0 0 PAG 1:115 X GRANT
51 2 0 0 PAG 1:112 X GRANT
51 2 0 0 PAG 1:113 X GRANT
51 2 0 0 EXT 1:112 X GRANT
51 2 1 2 KEY (8516877f1c72) X GRANT
51 2 0 0 IDX IDX: 2:469576711 X GRANT
51 2 469576711 0 TAB Sch-M GRANT
51 2 3 2 KEY (54016b2ccfff) X GRANT
51 2 1 3 KEY (0700b7f12a1d) X GRANT
51 2 3 2 KEY (4201380b1b46) X GRANT
51 1 85575343 0 TAB IS GRANT
51 2 3 2 KEY (5c013a008346) X GRANT
51 2 3 2 KEY (9f01b84495cc) X GRANT
51 2 3 1 KEY (110057368449) X GRANT
51 2 3 1 KEY (1000b999315b) X GRANT
51 2 3 2 KEY (eb002ad7df7e) X GRANT
51 2 3 1 KEY (0a00dd798c34) X GRANT
51 2 3 1 KEY (0d00ef895203) X GRANT
51 2 3 2 KEY (36027a4d788e) X GRANT
51 2 3 1 KEY (0c000126e711) X GRANT
51 2 1 1 KEY (0700c2f14c59) X GRANT
51 2 2 1 KEY (07000a2e7633) X GRANT
51 2 3 2 KEY (450124fb6f35) X GRANT
51 2 3 2 KEY (d7016cf8d9e5) X GRANT
52 5 0 0 DB S GRANT
53 5 0 0 DB S GRANT
The tempdb database has a dbid of 2, and the sysobjects table has an object ID of 1; sysindexes is object ID 2, and syscolumns is object ID 3. These locks being held on the system tables could lead to locking contention with other processes trying to read or update the tempdb system catalogs.
To minimize the potential for locking contention on the system tables in tempdb, consider creating your temp tables before starting the transaction so that locks are released immediately and not held on the system catalogs until the end of the transaction. If the table must be created in a transaction, commit your transaction as soon as possible.
Also, be aware that even if it's not in a transaction, creating a temporary table using SELECT INTO will hold locks on the system catalogs in tempdb until the SELECT INTO completes. If locking contention in tempdb becomes a problem, consider replacing SELECT INTO with CREATE TABLE followed by an INSERT using a SELECT statement. Although this might run a bit more slowly than SELECT INTO, the system table locks are held only for the brief moment it takes for CREATE TABLE to complete.
Another way to speed up temp table creation/population is to keep temporary tables as small as possible so they create more quickly. Select only the required columns, rather than SELECT *, and only retrieve the rows that you need. The smaller the temporary table, the faster it will be to create the table as well as to access the table.
If the temp table is of sufficient size and is going to be accessed multiple times within a stored procedure, it might be cost effective to create an index on it on the column(s) that will be referenced in the search arguments of queries against the temp table. The deciding factor of whether to create an index on a temporary table is if the time it takes to create the index plus the time the queries take to run using the index is less than the sum total of the time it takes the queries against the temporary table to run without the index.
The following example demonstrates the creation of an index on a temporary table:
use bigpubs2000 go create proc p1 WITH RECOMPILE as select title_id, type, pub_id, ytd_sales into #temp_titles from titles create index tmp on #temp_titles(pub_id) select sum(ytd_sales) from #temp_titles where pub_id = '0736' select min(ytd_sales) from #temp_titles where pub_id = '0736' return go
Some other final tips when using temporary tables in stored procedures:
Don't use temp tables to combine resultsets together when a UNION or UNION ALL will suffice. UNION ALL will be the fastest because no work table in tempdb is required to merge the resultsets.
Drop temporary tables as soon as possible to free up space in tempdb.
Consider using the table datatype to avoid tempdb usage altogether.
