[SQL SERVER][Performance] 注意隱示轉換 Part2
論壇上網友詢問WHERE 條件的執行規則，
該網友已經知道 SMALLDATETIME 的合理範圍，
但網友不解的事，為什麼查詢語法多加了一個條件( PER_ID = 'A' ) 反而就不會出現轉換溢位錯誤，
由於T-SQL並非程序性的語言，但如果你知道SQL Server是用什麼來表示查詢邏輯順序的話，
對於這類看似無解的問題其實透過執行計畫比較後方可略知一二，
還沒查看執行計畫之前，我腦中想像大概是這樣的(有一點想像力是很重要的...XD)。
1. PER_ID = 'A'
這條件會激起查詢最佳化程式使用 Index Seek 強烈無比的欲望(有相關正確索引前提下)
2.(CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
這條件會自動觸發隱示轉換(如果你不知道隱示轉換的話，可以看看我前一篇文章)，
且Convert函式也將導致使用 index scan 來處理，但查詢最佳化程式為了要使用 index seek，
所以可能會改寫原有TSQL來達到使用 index seek 目的。
下面我會比較兩者執行計畫來驗證我的假設是否正確。
CREATE TABLE [dbo].[test_DATA](
[PER_ID] NVARCHAR(50) NOT NULL,
[CARD_TIME] [datetime] NOT NULL,
CONSTRAINT [PK_test_DATA] PRIMARY KEY CLUSTERED
(
[PER_ID] ASC,
[CARD_TIME] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO test_DATA
SELECT 'A', '2012-08-26 18:24:00.000'
INSERT INTO test_DATA
SELECT 'A', '2012-08-27 18:24:00.000'
INSERT INTO test_DATA
SELECT 'A', '2012-08-01 18:24:00.000'
INSERT INTO test_DATA
SELECT 'A', '2012-08-31 18:24:00.000'
INSERT INTO test_DATA
SELECT 'B', '2012-08-26 18:24:00.000'
INSERT INTO test_DATA
SELECT 'B', '2012-08-27 18:24:00.000'
--模擬實際狀況插入一筆大於2079:06:06的紀錄
INSERT INTO test_DATA
SELECT 'A', '2123-08-26 18:24:00.000'
--發生溢位錯誤的查詢
SELECT *
FROM test_DATA
WHERE (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
執行計畫
SELECT * FROM test_DATA WHERE (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
|--Clustered Index Scan(OBJECT:([demo].[dbo].[test_DATA].[PK_test_DATA]),
WHERE:(CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)>=CONVERT_IMPLICIT(smalldatetime,[@1],0)
AND CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)<=CONVERT_IMPLICIT(smalldatetime,[@2],0)))
Argument:
OBJECT:([demo].[dbo].[test_DATA].[PK_test_DATA]),
WHERE:(CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)>=CONVERT_IMPLICIT(smalldatetime,[@1],0)
AND
CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)<=CONVERT_IMPLICIT(smalldatetime,[@2],0))
可以看到查詢最佳化程式使用 clustered index scan( PK_test_DATA full scan)作業，
並呼叫 CONVERT_IMPLICIT 函式自動轉換資料型別，
因為是 Full Scan 作業，所以會掃描基底資料表(一筆一筆逐一處理)，
所以只要某一筆資料內容範圍不符合 smalldatetime 範圍就會發生溢位錯誤，
透過查詢計畫查看這是很正常的結果。
--不會發生溢位錯誤的查詢(只多了一個PER_ID = 'A' 條件)
SELECT *
FROM test_DATA
WHERE PER_ID = 'A'
AND (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
執行計畫
SELECT * FROM test_DATA WHERE PER_ID = 'A' AND (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1013], [Expr1014], [Expr1015]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert(CONVERT_IMPLICIT(smalldatetime,[@2],0),NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1011],[Expr1012],[Expr1010])=GetRangeThroughConvert(NULL,CONVERT_IMPLICIT(smalldatetime,[@3],0),(42))))
| |--Constant Scan
|--Clustered Index Seek(OBJECT:([demo].[dbo].[test_DATA].[PK_test_DATA]),
SEEK:([demo].[dbo].[test_DATA].[PER_ID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)
AND [demo].[dbo].[test_DATA].[CARD_TIME] < [Expr1014]
AND [demo].[dbo].[test_DATA].[CARD_TIME] > [Expr1013]),
WHERE:(CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)>=CONVERT_IMPLICIT(smalldatetime,[@2],0)
AND CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)<=CONVERT_IMPLICIT(smalldatetime,[@3],0))
ORDERED FORWARD)
大家有看到查詢最佳化程式為了要使用 Clustered Index Seek，可說大幅度改寫原有TSQL甚至還多了兩個條件，
而這兩個條件就是讓查詢最佳化程式可以使用 Index Seek作業，且讓該查詢又不發生溢位錯誤的真正原因，
那麼 Expr1014和Expr1013又是什麼呢?下面讓我來解釋一下
Compute Scalar (計算純量)作業:
DEFINE:(([Expr1008],[Expr1009],[Expr1007])
=GetRangeThroughConvert(CONVERT_IMPLICIT(smalldatetime,[@2],0),NULL,(22)))
DEFINE:(([Expr1011],[Expr1012],[Expr1010])
=GetRangeThroughConvert(NULL,CONVERT_IMPLICIT(smalldatetime,[@3],0),(42)))
Clustered Index Seek (索引搜尋)作業:
OBJECT:([demo].[dbo].[test_DATA].[PK_test_DATA]),
SEEK:([demo].[dbo].[test_DATA].[PER_ID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)
AND [demo].[dbo].[test_DATA].[CARD_TIME] < [Expr1014]
AND [demo].[dbo].[test_DATA].[CARD_TIME] > [Expr1013]),
WHERE:(CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)>=CONVERT_IMPLICIT(smalldatetime,[@2],0)
AND
CONVERT(smalldatetime,[demo].[dbo].[test_DATA].[CARD_TIME],0)<=CONVERT_IMPLICIT(smalldatetime,[@3],0))
ORDERED FORWARD
因為查詢最佳化程式無法直接對 [CARD_TIME] 欄位使用 Index Seek(原TSQL針對該欄位使用了Convert函式)，
而且還必須進行資料型別的轉換 ( Convert Datetime to smalldatetime)，
但前面我有說過 PER_ID = 'A' 條件會激起查詢最佳化程式使用 Index Seek作業，
所以最佳化程式將大幅度改寫TSQL並進行資料型別的轉換，
但這樣的改寫加上資料型別轉換可能會造成查詢結果不正確，
所以最佳化程式先執行計算純量取得符合 smalldatetime 範圍，
確保原有 datetime 資料也在 smalldatetime 範圍內，
然後使用這計算出來的範圍邊界，針對該索引物件執行 Index Seek作業(少量資料執行隱示轉換)，
最後在和 smalldatetime 的資料比較，最終返回使用者所要求的資料結果集，
查詢最佳化程式，為了要避免 index scan並保有結果正確性，
採用大幅度改寫原有TSQL並計算該資料型別正確範圍邊界，
而這也是該查詢不會發生溢位錯誤的真正原因。
而這句TSQL被查詢最佳化程式改寫後應該如下
SELECT *
FROM test_DATA
WHERE (PER_ID = 'A' AND CARD_TIME < '20120831 00:00:00' AND CARD_TIME > '20120801 00:00:00' )
AND (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01' AND '2012/08/31')
針對搜尋datetime欄位相關建議可以查看 [SQL SERVER][Memo]搜尋datetime類型欄位三兩事，
但有沒有辦法來解決這問題呢?經過上面的測試，我們已經知道查詢最佳化程式為了要使用 Index Seek，
所以會偷偷加上兩個邊界條件，所以這問題當然是有解的。
create index idx_1 on test_DATA(CARD_TIME)
include(PER_ID)
SELECT *
FROM test_DATA with(INDEX(idx_1)) --使用hint 強制走idx_1 索引
--邊界條件記得加上時間，這樣查詢結果才會正確
WHERE (CONVERT(SMALLDATETIME, CARD_TIME) BETWEEN '2012/08/01 00:00:00' AND '2012/08/31 23:59:59')
參考
[SQL SERVER][Performance] 注意隱示轉換