資料表應用效能比較

最近比較頻繁的與TSQL 為伍，在讚嘆前人的花式語法之餘，有時也思考某幾個用法之間的差異，

或能區分相關的使用情境，這次針對泛暫存表的效能差異來比較一下。

泛暫存表有「資料表變數」、「暫存資料表」、「延伸資料表(CTE)」三種，下面都會提到。

由於需要查看分析，所以打開sql server 的分析功能，使用AdventureWorks2012 資料庫

下的相關BusinessEntity 資料表模擬 join 的效能，相較於實務狀況這邊的情境相對於單純。

使用「資料表變數」，語法如下

執行後，檢視分析訊息，觀察相關搜尋所使用IO 狀況，其中第一個為insert ，第二個為select join，

另外須注意 #BD90CCB7 為中繼資料表，先不納入考量，

將三者使用的IO紀錄如下：

資料表	掃描次數	邏輯讀取
BusinessEntity	1	98
BusinessEntityAddress	20777	41774

看一下執行計畫，同樣有分兩部分，一部分是 Insert 資料表變數，一部分是select join 的成本

我們關注select join 的執行計畫，將滑鼠移到處理方向圖示上方，可看出預估值與實際有明顯的落差，這會造成

執行計畫誤判，所以使用了巢狀迴圈，估計值也都不準確

如果你已經使用了資料表變數跟其他的資料表join，可在select 下方加入 option(recompile) ，

如下圖所示，這樣才可以讓資料表變數當下比較準確

重新執行

可看出BusinessEntityAddress 的IO 成本下降很多，下表列出該資料表兩次的差異

執行順序	掃描次數	邏輯讀取
沒使用option recompile 的BusinessEntityAddress	20777	41774
有使用option recompile 的BusinessEntityAddress	1	46

再看執行計畫，insert 沒差，查詢變很大，資料筆數預估不再是「1」了，並且走 hash 

使用「暫存資料表」，語法如下

同樣記得開啟分析以及執行計畫，先來看分析訊息

以上，可以看出來 select join 的成本很低

再來看執行計畫，如下圖，也都表現得非常突出，使用到hash 及合併

使用「延伸資料表(CTE)」，語法如下

看看分析，這邊 BusinessEntityAddress 多一點，但由於沒有 insert 動作，總IO量還是少的

看一下執行計畫，走合併路線

所以，由三種測試結果來看，優先使用「延伸資料表」> 「暫存資料表」，若不幸已經使用了

「資料表」變數，那就透過 option(recompile)