临时表 vs. 表变量及其对 SQL Server 性能的影响

引言

临时表和表变量之间存在三个主要的理论差异

CREATE table #T (…)
AND table-variables
DECLARE @T table (…)

开始吧

首先，表变量不记录事务日志。因此，它们不属于事务机制的范围，从这个例子中可以清楚地看到这一点

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 之后，我们将相同的“旧值”字符串分配给每一个。然后，我们开始一个更新它们内容的事务。此时，两者现在都将包含相同的“新值”字符串。但是当我们回滚事务时，如您所见，表变量 @T 保留了它的值，而不是恢复到“旧值”字符串。发生这种情况是因为，即使表变量在事务中更新，它也不是事务本身的一部分。

第二个主要区别是，任何带有临时表的存储过程都无法预编译，而带有表变量的存储过程的执行计划可以提前静态编译。预编译脚本可以大大提高其执行速度。对于长时间的存储过程来说，这种优势可能非常显著，因为重新编译的代价可能太高。

最后，表变量仅存在于与变量相同的范围内。与临时表相反，它们在内部存储过程和 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、10'000、100'000 变化到 1'000'000，它将给定数量的记录复制到一个临时表中（有一些例外，它会跳过 n 可以被 100 整除的记录），然后找到这些缺失记录的 max(s)。当然，我们给的记录越多，执行时间就越长。

为了精确地测量执行时间，我使用以下代码

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 到 1'000'000 (**) 如果执行时间太短，我会重复相同的循环 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 毫秒。 问题是“create index”语句强制 SQL Server 重新编译存储过程，并显着降低执行速度。

现在让我们尝试使用表变量来完成同样的事情

CREARE 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

令我们惊讶的是，此版本并没有比带有临时表的版本快多少。 这是 SQL Server 对于存储过程最开始的 create table #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
      WHEREre V.n=NUM.n)
GO

结果好多了，但 T2 胜过这个版本。

表 1，使用 SQL Server 2000，时间单位为毫秒

记录	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

但是当你尝试在 SQL Server 2005 上做同样的事情时，真正的震惊来了

表 2

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	太长了！	太长了！
1000000	10556	8673	6440	太长了！	太长了！

在某些情况下，SQL 2005 比 SQL 2000 快得多（用绿色标记）。 但在很多情况下，特别是对于大量数据，使用表变量的存储过程花费的时间要长得多（用红色突出显示）。 在四种情况下，我什至放弃等待了。

结论

1. 没有关于何时何地使用临时表或表变量的通用规则。 尝试它们并进行实验。
2. 在您的测试中，验证光谱的两侧 - 少量/记录数和大量数据集。
3. 当您在存储过程中使用复杂的逻辑时，请小心迁移到 SQL 2005。 相同的代码在 SQL Server 2005 上运行速度可能会慢 10-100 倍！