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

发布时间：2020-12-12 14:24:59 所属栏目：MsSql教程来源：网络整理

导读：在临时表 create table #T (…) 和表变量 declare @T table (…) 之间主要有 3 个理论上的不同。第一个不同使事务日志不会记录表变量。因此，它们脱离了事务机制的范围，从下面的例子可显而易见： create table #T (s varchar(128))? declare @T table (s

Records

0.7

13.5

0.6

0.8

100

1.2

1.7

14.2

1.3

1000

7.1

5.5

5.3

10000

100000

883

480

580

840

510

1000000

45056

6090

15220

20240

12010

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

但真正使我们震惊的是在SQLServer2005上的情形：

0.5

5.3

0.2

1.2

6.4

61.8

2.5

9.3

8.5

13.5

168

140

67.4

79.2

71.3

17133

13910

700

794

659

Too long!
Too long!

10556

8673

6440

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

在临时表
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 字符串。但当事务回滚时，正如你所看到的，表变量保留了这个新值而没有返回字符串。这是因为即使表变量在事务内被更新了，它本身不是事务的一部分。
第二个主要的不同是任何一个使用临时表的存储过程都不会被预编译，然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著，因为对它来说重新编译代价太高。
最后，表变量仅存在于那些变量能存在的相同范围内。和临时表相反，它们在内部存储过程和 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 ， 1000 10000 100000 到 1000000 不等来调用，它复制给定数量的记录到临时表（一些另外，它跳过那些能被整除的数值），然后找到缺失记录的最大值。当然，记录越多，执行的时间就越长：
为了测量正好的执行时间，使用下面的代码：
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
* ）表示程序里边的参数从不等。
** ）表示如果执行时间太短，就重复相同的循环次不等。
多次运行代码以获得执行的结果。
该结果在下面的表1里能找到。
下面试着给临时表添加一个主键来提升存储过程的性能：
create procedure T2?

?? ? @total int?
as?
? ? create table #T (n int primary key ,245)">然后，创建第三个。此时有聚集索引，它会工作得更好。但是是在插入数据到临时表之后创建的索引——通常，这样会更好：
T3?
@total int?
as?
? ? create table #T (n int,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
令人惊奇！大数据量花费的时间很长；仅仅添加条记录就花费了 13 毫秒。这个问题在于创建索引语句强迫 SQLServer 去重新编译存储过程，显著的降低了执行效率。
现在试着使用表变量来完成相同的事情：
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
使我们惊奇的是，该版本不是明显的比用临时表的快。这是由于在存储过程开头创建表语句时进行了特别优化的缘故。对整个范围内的值， V1 和工作得一样好。
下面试试有主键的情形：
create procedure? V2?
@total int?
as?
? ? declare @V table (n int ? ? 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 超过了该版本。