利用Table Varible做大資料量的處理,可能會有效能較差的隱憂

一直以來在寫TSQL時如需要一個暫存資料表,大多會用Table Varible或Temp Table來做

但常常聽到一些講師或看見官方文件都將Table Varible定義成存放小資料量的臨時資料表

但我長期使用Table Varible來做大資料的暫存表也沒遇見什大問題,因此也就沒多注意。

直到某天突然爆了一個大雷......

這一次的LAB我是用AdventureWorksDW2012資料庫來做。LAB很簡單,就是將大量資料分別倒入到一個Table Varible及一個Temp Table然後作簡易的DML並看看一些指標

首先我們先用Table Varible做測試,Code如下

--宣告Table變數@TbVarible
Declare @TbVarible TABLE(
[ProductKey] [int] NOT NULL,
[DateKey] [int] NOT NULL,
[MovementDate] [date] NOT NULL,
[UnitCost] [money] NOT NULL,
[UnitsIn] [int] NOT NULL,
[UnitsOut] [int] NOT NULL,
[UnitsBalance] [int] NOT NULL,
PRIMARY KEY CLUSTERED([ProductKey] ASC,[DateKey] ASC));

--將383019筆資料寫入到Table變數@TbVarible中
Insert Into @TbVarible 
Select * From [dbo].[FactProductInventory]
Where ProductKey < 300;

--針對Table變數@TbVarible做一個Query(筆數254919筆)
Select B.EnglishProductName,A.* From @TbVarible A
Inner Join [dbo].[DimProduct] B On A.ProductKey=B.ProductKey
Where A.ProductKey < 200;

 

上述語法的執行計畫如下,我們可以從下圖右下紅色圈選處看到,SQL估計該Table Varible的筆數是1筆,但右上紅色圈選的實際資料列數目卻是254919筆。由於SQL一開始預估筆數只有1筆,因此導致整個執行計畫在Inner Join的方式採用以迴圈方式進行(下圖左下紅色圈選處)。這表示DimProduct資料表要被蒐25萬多次。

 

下圖是整個IO的資料,如下圖紅色圈選處DimProduct資料表被IO了50幾萬個Page

 

耗費的時間,CPU 時間 = 655 ms,經過時間 = 1588 ms。

 

接下來改測試Temp Table,Code如下。

--宣告Temp Table #TbTemp
CREATE TABLE #TbTemp(
[ProductKey] [int] NOT NULL,
[DateKey] [int] NOT NULL,
[MovementDate] [date] NOT NULL,
[UnitCost] [money] NOT NULL,
[UnitsIn] [int] NOT NULL,
[UnitsOut] [int] NOT NULL,
[UnitsBalance] [int] NOT NULL,
PRIMARY KEY CLUSTERED([ProductKey] ASC,[DateKey] ASC));

--將383019筆資料寫入到#TbTemp中
Insert Into #TbTemp 
Select * From [dbo].[FactProductInventory]
Where ProductKey < 300;

--針對#TbTemp做一個Query(筆數254919筆)
Select B.EnglishProductName,A.* From #TbTemp A
Inner Join [dbo].[DimProduct] B On A.ProductKey=B.ProductKey
Where A.ProductKey < 200;

 

我們先看一下SQL對Temp Table的筆數預估值,如右下紅色圈選處,SQL預估是249799筆而實際上筆數是254919筆。因為統計值正確所以SQL執行計畫的Inner Join採用Merge的方式來做。

 

下圖是整個IO的資料,如下圖紅色圈選處DimProduct資料表只被IO了201個Page,跟Table Varible來比簡直是天差地遠。

 

耗費的時間(下圖紅色圈選處),CPU 時間只耗費78 ms(Table Varible則是655ms)。至於下面藍色圈選處是我刻意圈出來的,相同的測試方法,我們可以看見Temp Table多了一筆編譯時間(202ms)

這是因為資料表有大量資料異動引發了統計值的更新,而一旦資料表的統計被更新過,跟該Table相關的執行計畫就會被重新編譯。因此才會有多這樣一筆的編譯時間。

 

補充

無意間爬到一個文也是探討這樣的問題,網址如下 http://goo.gl/NUQ97m 。文中有提到可以利用OPTION(RECOMPILE)的方式來強迫SQL去追Table Varible的相關統計。

 

針對大資料的暫存資料表該用Temp Table還是Table Varible,大家應該有個方向了。統計值的正確與否是影響SQL執行效能的一個很大因素,因此小資料量放Table Varible而大資料量還是用Temp Table來做吧。

 

 

 

我是ROCK

rockchang@mails.fju.edu.tw