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 Procedurescreate 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 Mnchen 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.
Temporary Table Performance TipsAll 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 Tablebegin 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:
|