RiCo技術農場

這是我以前一個 SQL Tuning 案子中所遇到的問題，

而我之前分享 SQL Tuning --SQL撰寫準則中也有demo隱示轉換對查詢效能的影響

(自古以來，大部分程式自動處理作業通常都需要付出一些效能代價的...)，

今天剛好又遇到該問題，為了不浪費自己太多口水和時間，

乾脆寫下整各測試過程以後直接分享文章給使用者較快。

 

問題:

查詢符合SARG也有相對應索引，但查詢卻還是使用 index scan，造成前端查詢相當緩慢。

 

原因:

開發人員大部分查詢 where 條件內容都會加上單引號

(或者少加上單引號)，說簡單一點，就是沒有依照資料行資料型別來撰寫相關正確內容資料型別，

這樣就會讓SQL Server自動執行隱示轉換處理，

下面舉個例子，大家應該會更明白一點。

 

測試:

NationalIDNumber 資料型別 nvarchar(15)

 

查詢語法

SELECT EmployeeID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = 954276278

 

 

可以看到掃描計數 1，邏輯讀取4，執行計畫採用索引掃描實體(邏輯)作業，

整體的查詢計畫成本為 0.0043707，但明明該TSQL有相關正確索引，且也符合SARG格式，

可是為什麼沒有採用索引搜尋作業呢??

我們詳細看一下述詞的部份，索引掃描實體(邏輯)作業因為呼叫 Convert_Implicit 函式，

將資料行隱示轉換為 int 資料型別(為了和條件內容資料型別匹配)，

而這樣的做法如同針對 where 欄位使用函數，

雖然表面上的TSQL看起來好像符合SARG格式，

但實質上根本就不符合SARG格式，因為不符合SARG格式，

所以造成查詢最佳化程式採用索引掃描實體(邏輯)作業

(畢竟查詢最佳化程式也是人寫出來的程式，別人的程式規則還是要遵守的...XD)。

 

既然知道了原因，所以我請開發人員在撰寫相關TSQL時，

請一定要配合該資料行的資料類型撰寫正確資料型別，

不要貪圖一時方便而埋下效能地雷，修改如下。

 

SELECT EmployeeID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = '954276278'

 

可以看到這次終於採取了正確的索引搜尋實體(邏輯)作業，

掃描計數 0，邏輯讀取2，I/O部分大大減少，

整體查詢成本也降低為0.0032831。

 

note:SQL Server在處理隱示轉換是有優先順序的(低優先順序會被轉換為高優先順序)，

這部份可以參考 資料類型優先順序 (Transact-SQL)，

由於這很容易自行測試驗證，所以這裡我也不在多做測試，

雖然每到農曆7月期間，Server和程式會有很多怪問題無法解釋，

但....有些問題其實是自己造成而不知的~~~

 

 

 

 

參考

資料類型優先順序 (Transact-SQL)