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

2016-02-19

949
0
[SQL]效能
2016-02-19

一直以來在寫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的方式來做。