[SQL SERVER][Performance]避免區域變數

2013-03-26

3027
0
SQL_SERVER

今天在SQL PASS TW上看到一個查詢效能問題，

個人3月份分享查詢調校不求人有提過，撰寫TSQL要盡量避免區域變數，

因為當你使用區域變數時，查詢最佳化程式無法知道編譯時期(compile time)該值是多少，

所以會造成統計值誤用估算誤差很大，估算誤差越大將選用到不好的執行計畫，

因為該區域變數值只能在執行時期(Runtime)得知，

但如果你一定要使用區域變數怎麼辦?

是否可以讓查詢最佳化程式知道編譯時期的區域變數呢?

答案是可以的，讓我簡單模擬該網友問題並解決它。

使用區域變數宣告

declare @startId nvarchar(30),@endid nvarchar(30)
set @startId =N'Y980006618239'
set @endid=N'Y980006767250'
select BILL_NO,POLICY_NO,BILL_STATUS_CODE
from BILL
where BILL_NO>=@startId and BILL_NO<=@endid

logical reads=13379 、整體執行時間 281+120 =401ms。

執行計畫中，可以看到實際資料列數目和估計相差很大。

整體成本12.0983。

如我前面所說，因為使用區域變數，所以編譯時期無法得知該變數值(只能在執行期間得知)，

這將造成統計值誤差變大(實際值只有78筆，但估算比數卻高達180000)，估計筆數讓查詢最佳化程式以為是大結果集，

而且查詢成本超過一般序列執行計畫成本，所以採用平行計畫來處理並選擇不良執行計畫，

導致整體成本拉高(整體執行時間拉長)、邏輯讀取變多。

直接帶值不使用區域變數

select BILL_NO,POLICY_NO,BILL_STATUS_CODE
from BILL
where BILL_NO>=N'Y980006618239' and BILL_NO<='Y980006767250'

logical reads=13 。

執行計畫中可以看到這次選擇clustered index。

整體成本0.0032831。

直接帶值可以讓查詢最佳化程式在compile time知道該值內容，

這樣可以比較精準透過統計值來估算相關資料筆數，並且選用較佳執行計畫，

所以你在撰寫TSQL時，應該要盡量避免區域變數的使用，

但我就是想使用，是否有辦法解決呢?請看下面

使用 sp_executesql 執行

declare @startId nvarchar(30),@endid nvarchar(30)
set @startId =N'Y980006618239'
set @endid=N'Y980006767250'
declare @myquery nvarchar(4000); 
set @myquery = 'select BILL_NO,POLICY_NO,BILL_STATUS_CODE
from BILL
where BILL_NO>=@startId and BILL_NO<=@endid';
execute sp_executesql @myquery,N'@startId nvarchar(30) ,@endid nvarchar(30)',
@startId,@endid