搜尋本站文章

2017-10-14

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)

在使用 WHERE 搜尋條件 去篩選 日期時間 資料型態,建議:

  1. 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
    • 例如:>= (大於或等於),  <= (小於或等於), < (小於)。
  2. 避免使用 CONVERT/CAST 或 BETWEEN。

目的是:
  1. 符合 搜尋引數(Search Arguments, SARGs) ,效能優化。
  2. 資料正確性,沒有因 不正確的假設導致不正確的結果。

建議用法:
  • 當 WHERE 搜尋條件 去篩選 日期時間 資料型態時
  • 使用 比較運算(Comparison Operators) 來篩選出明確的日期時間範圍
    • 例如:>= (大於或等於),  <= (小於或等於), < (小於)。
-- 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
-- 例如:>= (大於或等於),  <= (小於或等於), < (小於)
-- 需求:篩選 20140805 當日的資料,回傳 3 rows
SELECT TransactionID, ProductID, Quantity, TransactionDate 
FROM dbo.Transactions02
WHERE TransactionDate >= '20140805' AND TransactionDate<'20140806'

使用 比較運算子,符合 SARG,使用到索引
無須額外耗用 CONVERT/CAST 轉型

WHERE TransactionDate >= '20140805' AND TransactionDate '20140806'


對照 Subtree Cost(子樹成本)

語法 Subtree Cost(子樹成本)
使用 比較運算子,符合 SARG,使用到索引 0.104689
額外使用 CONVERT/CAST 0.722386

可以看到明顯相差達 7 倍。



資料表 Transactions01 的 TransactionDate 沒有索引

--01. 檢視資料特性_Datetime

TransactionDate 資料行的資料類型是:Datetime


若需求是 篩選出 20140805 當日 的交易資訊,預期應該有 3 筆資料列符合回傳。

但是 WHERE 條件式寫成:WHERE TransactionDate = '20140805',省略了時間值,這是篩選出 2014-08-05 00:00:00:000 時間點的資料值。
因此,僅回傳 1 筆資料列。

-- 02_只用等號_卻是僅回傳 1 row



額外 再去使用 CONVERT 或 CAST 將 DateTime 資料轉型為 字串資料類型後做比對:
  1. 是可以正確搜尋到 3 筆資料列。
  2. 但這是 違反符合 SARGs,導致 無法使用到索引。
  3. SQL Server Query Engine 也因為此資料類型的轉換,進而影響到 查詢計劃選擇中的 "CardinalityEstimate" 或 "SeekPlan"。
WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 03_CONVERT 轉換 DateTime 資料型別 為 字串


觀察 Execution Plan,可以看到特別顯示 驚嘆號!

-- 04_Execution_Plan_issue


執行計畫內的警告訊息:
警告:

  • 運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)) 中的類型轉換可能會影響到查詢計劃選擇中的 "CardinalityEstimate", 
  • 運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)=[@1]) 中的類型轉換可能會影響到查詢計劃選擇中的 "SeekPlan"


-- 05_Execution_Plan_issue


-- 06_Execution_Plan_issue


使用 SentryOne Plan Explorer,亦可觀察到 Warnings 訊息。

-- 07_Execution_Plan_issue



建議用法

  • 使用 比較運算(Comparison Operators) 來篩選出明確的日期時間範圍
  • 例如:>= (大於或等於),  <= (小於或等於), < (小於)
WHERE TransactionDate >= '20140805' AND TransactionDate '20140806'

-- 08_沒有警告訊息的Exection_Plan


-- 09_沒有警告訊息的Exection_Plan





使用「搜尋引數(Search Argument,SARG)」

資料表 Transactions02 的 TransactionDate 索引

-- 10_資料表 Transactions02 的 TransactionDate 有索引


不符合 SARG 規範,導致無法使用索引。
WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 11_Non SARG 01


-- 12_Non SARG 01_Execution_Plan


不符合 SARG 規範,導致無法使用索引
WHERE DATEPART(yyyy,TransactionDate)=2014 AND DATEPART(mm,TransactionDate)=8 AND DATEPART(dd,TransactionDate) = 5

-- 13_Non SARG 02


-- 14_Non SARG 02_Execution_Plan


建議語法:符合 SARG,可以使用到索引

WHERE TransactionDate >= '20140805' AND TransactionDate '20140806'

-- 015_SARG_Index


-- 016_SARG_Index_Execution_Plan




範例程式碼

20171014_WHERE_DATE_Compare_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40MDNoMGF4Wml6M00?usp=sharing



參考資料

What do BETWEEN and the devil have in common?
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

It's Time for Time
http://www.kimballgroup.com/1997/07/its-time-for-time/

INF: Search Arguments That Determine Distribution Page Usage
http://support.microsoft.com/kb/169642/en-us

SQL Server 2005 Compact Edition Books Online
Query Performance Tuning (SQL Server Compact Edition)
http://msdn.microsoft.com/en-us/library/ms172984.aspx

Comparison Operators (Transact-SQL)
https://technet.microsoft.com/EN-US/library/ms188074(v=sql.105).aspx

SentryOne Plan Explorer | SQL Query Optimization
https://www.sentryone.com/plan-explorer

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (2)
http://sharedderrick.blogspot.tw/2017/10/sql-server-where-search-condition-with_15.html



沒有留言:

張貼留言