临时表vs.表变量以及它们对SQLServer性能的影响

--王成辉翻译整理，转贴请注明出自微软BI开拓者http://www.windbi.com/
--原帖地址


在临时表
create table #T (…)
和表变量
declare @T table (…)
之间主要有3个理论上的不同。

第一个不同使事务日志不会记录表变量。因此，它们脱离了事务机制的范围，从下面的例子可显而易见：

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 @

在声明临时表#T和表变量@T之后，给它们分配一个相同的值为old value字符串。然后，开始一个事务去更新它们。此时，它们都将有新的相同的值new value字符串。但当事务回滚时，正如你所看到的，表变量@T保留了这个新值而没有返回old value字符串。这是因为即使表变量在事务内被更新了，它本身不是事务的一部分。

第二个主要的不同是任何一个使用临时表的存储过程都不会被预编译，然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著，因为对它来说重新编译代价太高。

最后，表变量仅存在于那些变量能存在的相同范围内。和临时表相反，它们在内部存储过程和exec（string）语句里是不可见的。它们也不能在insert/exec语句里使用。

性能比较

首先，准备一个有100万记录的测试表：

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

准备测试存储过程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

使用参数从10，100，1000，10000，100000到1000000不等来调用，它复制给定数量的记录到临时表（一些另外，它跳过那些能被100整除的数值），然后找到缺失记录的最大值。当然，记录越多，执行的时间就越长：
为了测量正好的执行时间，使用下面的代码：

declare @t1 datetime, @n int

set @t1=getdate()
set @n=100 – (**)
while @n>0 begin
    exec T1 1000 – (*)
    set @n=@n-1 end
select datediff(ms,@t1,getdate())
GO

（*）表示程序里边的参数从10到1000000不等。
（**）表示如果执行时间太短，就重复相同的循环10到100次不等。

多次运行代码以获得执行的结果。

该结果在下面的表1里能找到。

下面试着给临时表添加一个主键来提升存储过程的性能：

create procedure T2

    @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

然后，创建第三个。此时有聚集索引，它会工作得更好。但是是在插入数据到临时表之后创建的索引——通常，这样会更好：

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

令人惊奇！大数据量花费的时间很长；仅仅添加10条记录就花费了13毫秒。这个问题在于创建索引语句强迫SQLServer去重新编译存储过程，显著的降低了执行效率。

现在试着使用表变量来完成相同的事情：

create procedure V1
    @total int
as
    declare @V table (n int, 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

使我们惊奇的是，该版本不是明显的比用临时表的快。这是由于在存储过程开头创建表#T语句时进行了特别优化的缘故。对整个范围内的值，V1和T1工作得一样好。

下面试试有主键的情形：

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

这个结果很快，但T2超过了该版本。

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

表1：使用SQLServer2000，时间单位毫秒

但真正使我们震惊的是在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!

表2：使用SQLServer2005（时间单位毫秒）

有时，SQL2005比SQL2000快（上面标记为绿色的部分）。但大多数情况下，特别是在数据量巨大时，存储过程使用表变量花费了更长的时间（红色部分）。在4种情形下，我甚至放弃了等待。

结论

• 在什么时候和什么地方使用临时表或表变量没有一个普遍的规则。试着都测试测试它们。
• 在你的测试里，少量的记录和大量的数据集都要进行测试。
• 当在你的存储过程里使用了复杂的逻辑的时候要小心迁移到SQL2005。相同的代码在SQLServer2005上可能运行要慢10到100倍