SQLServer臨時表和表變數系列之踢館篇

風移發表於2017-02-04
SQLServer變數

摘要

在面對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 logic
CREATE 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語句測試結果截圖如下：
07.png
原作者得出的結論：“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.”
這個結論不攻自破了，由於作者沒有對錶變數建立索引，所以會認為臨時表的索引導致了插入效率比表變數低。來看看糾正了測試環境的錯誤後的測試結果：
08.png
從這個測試結果來看，表變數有一條插入語句比臨時表更慢；另一條插入語句兩者效能相當。

SELECT語句效能對比

原作者對SELECT語句測試結果的截圖如下（我用紅色長方形框標記了對比之處）：
01.png
還是由於原作者對錶變數沒有建立索引，導致通過col2條件查詢表變數的時候，得出的測試結論不正確：使用臨時表的時間消耗為1毫秒，使用表變數時間消耗為99毫秒，臨時表效率更高。我的測試結果截圖如下：
02.png
從我的測試接過來看，使用臨時表的時間消耗為626毫秒，而使用表變數的時間消耗僅為100毫秒，表變數效率更高，這個和原作者的結論恰好相反。

UPDATE語句效能對比

UPDATE語句由於原作者截圖中無法看到執行時間消耗。所以，我們採用CPU和Reads來對比：原測試使用表變數在CPU和IO讀兩個方面相對於臨時表效能消耗更高，效率更低。
03.png
而我們的測試結果恰好相反，兩者在CPU方面相當，均為0，而IO讀取方面，表變數效能更高，與原作者結論恰好相反。得出這個測試結果的原因還是因為為表變數建立了索引。
04.png

DELETE語句效能對比

原作者對DELETE語句測試的截圖如下。由截圖來看，表變數在CPU消耗，IO讀取和執行時間消耗三個層面，效能消耗相對於臨時表都更高，效率都更低。
05.png
而我們的測試結果截圖如下：臨時表CPU消耗更嚴重，IO讀表變數更高，執行時間表變數稍微高一點，1毫秒的差異幾乎可以忽略不計。
06.png

總結

之所謂“差之毫釐謬以千里”，由於原作者忽略了對錶變數定義索引，亦或者是不知道為表變數建立索引，導致整個效能對比測試不嚴謹，測試結果和事實大相徑庭，給了我們踢館的機會。


相關文章