临时表vs.表变量以及它们对SQLServer性能的影响 SQL SERVER,商业智能,BI,DBA - 微软BI开拓者

个人空间
组别:管理员
性别:
来自:北京海淀
积分:1121
帖子:619
注册: 2007-05-23

2007-05-24 16:23 | 只看楼主

树型| 收藏| 小中大 1

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

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

--王成辉翻译整理，转贴请注明出自微软BI开拓者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倍。

虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。

个人空间
组别:管理员
性别:
来自:北京海淀
积分:1121
帖子:619
注册: 2007-05-23

2008-10-13 15:51 | 只看楼主

树型| 收藏| 小中大 2

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

表变量从不产生统计信息，所以优化器总是假定它只有一条记录，而不管它们实际上有多少条。

临时表可以产生统计信息，和永久表一样存储在柱状图里，以备优化器使用。

－－－见于《Dissecting SQL Server Execution Plans》的17页与18页之间

拓狼最后编辑于 2008-10-13 15:53:13

虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。

zsforever 个人空间组别:中级会员性别: 来自: 积分:251 帖子:110 注册: 2007-07-11	2008-10-14 11:10 \| 树型\| 收藏\| 小中大 3 回复：临时表vs.表变量以及它们对SQLServer性能的影响 sp里使用临时表时，如果表行数会因为参数的不同有很大变化，会有重编译的问题

lsw 个人空间组别:新手上路性别: 来自: 积分:20 帖子:2 注册: 2008-11-04	2008-11-04 17:34 \| 树型\| 收藏\| 小中大 4 回复：临时表vs.表变量以及它们对SQLServer性能的影响

个人空间
组别:中级会员
性别:
来自:
积分:105
帖子:13
注册: 2008-10-22

2008-11-05 02:19 |

树型| 收藏| 小中大 5

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

每种临时对像都有自己的优点，表变量的优势是它所涉及的重新编译次数较少。缺点拓狼提到了，因为缺少分布统计信息，当优化器需要用直方图确定选择性时，可能会得到低效的查询计划。特别是当数据量大的时候尤为严重。
感觉数据量少的时候可以用表变量，而重要的查询用临时表。但当你大量使用临时表时，可能你得多多注意你的tempdb，它可能会成为你系统中的瓶颈。

以上观点参考自《Inside Microsoft SQL Server 2005 T-SQL Programming》

个人空间
组别:中级会员
性别:
来自:
积分:105
帖子:7
注册: 2008-01-03

2008-11-17 14:50 |

树型| 收藏| 小中大 6

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

求助！！！
create procedure T1
@total int
as
create table #T (n int, s varchar(128))
insert into #T select * 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
)

报错： Server: Msg 213, Level 16, State 5, Procedure T1, Line 5
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 207, Level 16, State 1, Procedure T1, Line 7
Invalid column name 'n'.

liuabc0719 个人空间组别:新手上路性别: 来自: 积分:37 帖子:9 注册: 2008-11-19	2009-11-12 21:24 \| 树型\| 收藏\| 小中大 7 回复：临时表vs.表变量以及它们对SQLServer性能的影响佩服。狼哥太强了，谢谢!

es8d 个人空间组别:新手上路性别: 来自: 积分:22 帖子:19 注册: 2009-07-02	2009-11-16 22:09 \| 树型\| 收藏\| 小中大 8 回覆：臨時表vs.表變量以及它們對SQLServer性能的影響學習了收藏起來

fuxiaoyang13 个人空间组别:中级会员性别: 来自: 积分:264 帖子:62 注册: 2008-07-12	2009-11-17 11:58 \| 树型\| 收藏\| 小中大 9 回复：临时表vs.表变量以及它们对SQLServer性能的影响 xuexi !

hanjoe109 个人空间组别:新手上路性别: 来自: 积分:7 帖子:26 注册: 2008-03-31	2010-03-05 01:10 \| 树型\| 收藏\| 小中大 10 回复: 临时表vs.表变量以及它们对SQLServer性能的影响引用: 原帖由阿少于 11/5/2008 2:19:00 AM 发表但当你大量使用临时表时，可能你得多多注意你的tempdb，它可能会成为你系统中的瓶颈。以上观点参考自《Inside Microsoft 謝謝提醒！

微软BI开拓者数据库专区 SQL Server管理临时表vs.表变量以及它们对SQLServer性能的影响

[技术文档] 临时表vs.表变量以及它们对SQLServer性能的影响