【转载】SQL Server 临时表和表变量系列之踢馆篇

摘要

在面对SQL Server选择使用临时表还是表变量作为数据暂存问题时，有一个非常重要的选择标准便是性能，两者对于查询语句和DML性能表现到底如何呢？我相信，很多人的认识是片面的，或者是错误的。这里以一篇引用率很高的文章来作为反面教材来纠正那些片面和错误的认识，我暂且称之为“踢馆”。

背景

在研究临时表和表变量该如何选择的时候，一篇文章叫着SQL Server Temp Table vs Table Variable Performance Testing文章引用率是非常高的。通读全文，作者褒“临时表”贬“表变量”的语调跃然纸上，虽然原作者也有唯物辩证的思维去看待这个问题。但，综合起来原作者认为临时表性能好于表变量。事实上真的是这样子的吗？这篇文章会一探究竟。

分析

首先，原作者的测试环境的搭建存在漏洞，非常不严谨。体现在作者为临时表在col2上创建了非聚集索引，而表变量上的col2上没有定义非聚集索引。根据生物学对比试验方法论，实验组与对比组只允许有一个变化的影响因素，但是原作者这里引入了两个变化因素：第一是临时表和表变量；第二个变化因素是临时表具有索引，而表变量没有索引。这可能是因为作者认为表变量不能够创建索引，证据在原作者的这句话“However, when we query rows using the indexed column of the temporary table, which is not indexed in the table variable since this is not available for table variables, we see a really big increase in performance across all measures for the temporary table. ”，但实际上表变量同样是可以创建索引的，只不过必须是在定义表变量的同时创建索引，定义结束后，不支持对表变量的任何修改。详情可以参见文章SQL Server 临时表和表变量系列之认知误区篇。由于作者的这个认识误区，导致了整个测试结果不严谨，结论不够准确，给了读者踢馆的机会。
原作者对临时表创建的两个索引：

-- Create index logicCREATE NONCLUSTERED INDEX [IX_temptable] ON #temptable ([col2] ASC)CREATE NONCLUSTERED INDEX [IX_bigtemptable] ON #bigtemptable ([col2] ASC)

测试

毛爷爷说：“实践出真知，没有调查就没有发言权。”，我们按照原作者的思路，纠正作者测试环境搭建的疏漏，然后再来测试INSERT、SELECT、UPDATE和DELETE操作性能。为两个表变量在col2上创建索引，定义表变量时，添加了语句index IX_col2(col2 ASC)。代码如下：

-- Table creation logic

CREATE TABLE #temptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,

[col3] [int] NULL,[col4] [varchar](50) NULL)

DECLARE @tablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,

[col3] [int] NULL,[col4] [varchar](50) NULL, index IX_col2(col2 ASC))

CREATE TABLE #bigtemptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,

[col3] [int] NULL,[col4] [varchar](50) NULL)

DECLARE @bigtablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,

[col3] [int] NULL,[col4] [varchar](50) NULL, index IX_col2(col2 ASC))

-- Create index logic

CREATE NONCLUSTERED INDEX [IX_temptable] ON #temptable ([col2] ASC)

CREATE NONCLUSTERED INDEX [IX_bigtemptable] ON #bigtemptable ([col2] ASC)

保持其他的地方原封不动，在一个连接中，执行所有的语句。

INSERT语句性能对比

原作者对INSERT语句测试结果截图如下：

                               
登录/注册后可看大图

原作者得出的结论：“This can probably be attributed to the fact that the temporary table has an extra index on the table that needs to be updated as well.”
这个结论不攻自破了，由于作者没有对表变量创建索引，所以会认为临时表的索引导致了插入效率比表变量低。来看看纠正了测试环境的错误后的测试结果：

                               
登录/注册后可看大图

从这个测试结果来看，表变量有一条插入语句比临时表更慢；另一条插入语句两者性能相当。

SELECT语句性能对比

原作者对SELECT语句测试结果的截图如下（我用红色长方形框标记了对比之处）：

                               
登录/注册后可看大图

还是由于原作者对表变量没有创建索引，导致通过col2条件查询表变量的时候，得出的测试结论不正确：使用临时表的时间消耗为1毫秒，使用表变量时间消耗为99毫秒，临时表效率更高。我的测试结果截图如下：

                               
登录/注册后可看大图

从我的测试接过来看，使用临时表的时间消耗为626毫秒，而使用表变量的时间消耗仅为100毫秒，表变量效率更高，这个和原作者的结论恰好相反。

UPDATE语句性能对比

UPDATE语句由于原作者截图中无法看到执行时间消耗。所以，我们采用CPU和Reads来对比：原测试使用表变量在CPU和IO读两个方面相对于临时表性能消耗更高，效率更低。

                               
登录/注册后可看大图

而我们的测试结果恰好相反，两者在CPU方面相当，均为0，而IO读取方面，表变量性能更高，与原作者结论恰好相反。得出这个测试结果的原因还是因为为表变量创建了索引。

                               
登录/注册后可看大图

DELETE语句性能对比

原作者对DELETE语句测试的截图如下。由截图来看，表变量在CPU消耗，IO读取和执行时间消耗三个层面，性能消耗相对于临时表都更高，效率都更低。

                               
登录/注册后可看大图

而我们的测试结果截图如下：临时表CPU消耗更严重，IO读表变量更高，执行时间表变量稍微高一点，1毫秒的差异几乎可以忽略不计。

                               
登录/注册后可看大图