Temporary tables vs. Table variables and their impact on SQL Server Performance

Temporary tables vs. Table variables and their impact on SQL Server Performance

-- Wang Chenghui translation finishing, post please indicate from Microsoft Bi pioneer http://www.windbi.com/
--Original post address


In the temporary table
Create Table # T (...)
And table Variables
Declare @ t table (...)
There are three major differences in theory.

FirstTransaction logs do not record table variables. As a result, they are out of the scope of the transaction mechanism and can be seen from the following example:

Create Table # T (s varchar (128 ))
Declare @ t table (s varchar (128 ))
Insert into # T select 'old value #'
Insert into @ t select 'old value @'
Begin transaction
Update # t set S = 'new value #'
Update @ t set S = 'new value @'
Rollback transaction
Select * from # T
Select * From @ t

S
---------------
Old value #

S
---------------
New value @

After declaring the temporary table # T and the table variable @ T, assign them an old value string with the same value. Then, start a transaction to update them. At this time, both of them will have the same new value string. But when the transaction is rolled back, as you can see, the table variable @ t retains the new value without returning the old value string. This is because even if the table variable is updated in the transaction, it is not a part of the transaction.

Second The main difference is that any stored procedure using a temporary table will not be pre-compiled. However, the execution plan of the stored procedure using Table variables can be pre-compiled statically. The main advantage of precompiling a script is that it accelerates execution. This benefit is even more significant for long stored procedures because re-compilation is too costly.

Last Table variables exist only in the same range that can exist. Unlike temporary tables, they are invisible in internal stored procedures and exec (string) statements. They cannot be used in insert/exec statements.

Performance Comparison

First, prepare a test table with 1 million records:

Create Table num (N int primary key, s varchar (128 ))
Go
Set nocount on
Declare @ n int
Set @ n = 1000000
While @ n> 0 begin
Insert into num
Select @ n, 'value: '+ convert (varchar, @ n)
Set @ n = @ n-1
End
Go

Prepare to test the Stored Procedure T1:

Create procedure T1
@ Total int
As
Create Table # T (N int, s varchar (128 ))
Insert into # T select N, S from num
Where n % 100> 0 and n <= @ total
Declare @ res varchar (128)
Select @ res = max (s) from num
Where n <= @ total and
Not exists (select * from # T
Where # T. N = num. N)
Go

The parameter is called from 10,100,100, 100000, 1000000 to 100. It copies a given number of records to a temporary table (In addition, It skips the values that can be divisible ), find the maximum value of the missing record. Of course, the more records, the longer the execution time:
To measure the execution time, use the following Code :

Declare @ T1 datetime, @ n int

Set @ T1 = getdate ()
Set @ n =100 -(**)
While @ n> 0 begin
Exec T11000 -(*)
Set @ n = @ n-1 end
Select datediff (MS, @ T1, getdate ())
Go

(*) IndicatesProgramThe value range is 10 to 1000000.
(**) Indicates that if the execution time is too short, the same loop will be repeated for 10 to 100 times.

Run the code multiple times to obtain the execution result.

This result can be found in Table 1 below.

Next, we try to add a primary key to the temporary table to improve the performance of the stored procedure:

Create procedureT2

@ Total int
As
Create Table # T (N int Primary Key , S varchar (128 ))
Insert into # T select N, S from num
Where n % 100> 0 and n <= @ total
Declare @ res varchar (128)
Select @ res = max (s) from num
Where n <= @ total and
Not exists (select * from # T
Where # T. N = num. N)
Go

Then, create the third. A clustered index will work better. But it is the index created after the data is inserted into the temporary table -- normally, this will be better:

Create procedure T3
@ Total int
As
Create Table # T (N int, s varchar (128 ))
Insert into # T select N, S from num
Where n % 100> 0 and n <= @ total
Create clustered index tind on # T (N)
Declare @ res varchar (128)
Select @ res = max (s) from num
Where n <= @ total and
Not exists (select * from # T
Where # T. N = num. N)
Go

Amazing! Large data volumes take a long time; it takes 13 milliseconds to add 10 records. This problem is that the index creation statement forces sqlserver to re-compile the stored procedure, significantly reducing the execution efficiency.

Now try to use table variables to do the same thing:

Create procedure V1
@ Total int
As
Declare @ V Table (N int, s varchar (128 ))
Insert @ V Select N, S from num
Where n % 100> 0 and n <= @ total
Declare @ res varchar (128)
Select @ res = max (s) from num
Where n <= @ total and
Not exists (select * from @ V v
Where V. N = num. N)
Go

We are surprised that this version is not obviously faster than using a temporary table. This is because the table # T statement is created at the beginning of the stored procedure with special optimization. For values in the entire range, V1 and T1 work as well.

Try the following scenario with a primary key:

Create procedure V2
@ Total int
As
Declare @ V Table (N int Primary Key , S varchar (128 ))
Insert into @ v select N, S from num
Where n % 100> 0 and n <= @ total
Declare @ res varchar (128)
Select @ res = max (s) from num
Where n <= @ total and
Not exists (select * From @ V v
Where V. N = num. N)
Go

This result is fast, but T2 is later than this version.

Records	T1	T2	T3	V1	V2
10	0.7	1	13.5	0.6	0.8
100	1.2	1.7	14.2	1.2	1.3
1000	7.1	5.5	27	7	5.3
10000	72	57	82	71	48
100000	883	480	580	840	510
1000000	45056	6090	15220	20240	12010

Table 1: sqlserver2000, in milliseconds

But what really shocked us was the situation on sqlserver2005:

N	T1	T2	T3	V1	V2
10	0.5	0.5	5.3	0.2	0.2
100	2	1.2	6.4	61.8	2.5
1000	9.3	8.5	13.5	168	140
10000	67.4	79.2	71.3	17133	13910
100000	700	794	659	Too long!	Too long!
1000000	10556	8673	6440	Too long!	Too long!

Table 2: sqlserver2005 (unit: milliseconds)

Sometimes, sql2005 is faster than SQL2000 (marked as green above ). But in most cases, especially when the data volume is huge, it takes longer (in red) to use table variables in the stored procedure ). In four cases, I even gave up waiting.

Conclusion

• There is no common rule when and where to use temporary tables or table variables. Test them all.
• In your test, a small amount of records and a large number of datasets must be tested.
• When using complex logic in your stored procedures, be careful when migrating to sql2005. The same Code may be 10 to 100 times slower to run on sqlserver2005.