星期五, 9月 27, 2013

[SQL] WHERE 中的資料篩選

效能議題中,有個原則是不要對欄位進行任何轉換,而最常見的轉換莫過於利用 YEAR()、DATEADD() 、CONVERT() 等函數,對日期欄位進行轉換。
  • 利用 AdventureWorks2012 的 SalesOrderHeader Table 說明
-- 針對 OrderDate 建立 Index
CREATE INDEX [IX_SalesOrderHeader_OrderDate] ON dbo.SalesOrderHeader (OrderDate)

-- 跑這 4 個 T-SQL 語法來觀察執行計畫中 Index 使用情況
SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE SUBSTRING(CONVERT(varchar(10),OrderDate,112),1,4) = '2007'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE CONVERT(varchar(10),OrderDate,112) LIKE '2007%'

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE DATEPART(yyyy,OrderDate) = 2007

SELECT OrderDate
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20070101' AND '20071231'
  • T-SQL 執行結果和其執行計畫
[SQL] WHERE 中的資料篩選-1
從上圖中就可以觀察到只要對 OrderDate 日期欄位進行任何轉換動作,都會導致 Query Optimizer 使用 Index Scan,而非 Index Seek 來抓取資料,造成效能低落,T-SQL 語法中假如常常需要進行日期轉換,或許可以考慮除了日期欄位本身外,還可以建立年、月、日資料欄位,來增加資料蒐尋的便利性。
  • 2011 SQL Hero 考題
[SQL] WHERE 中的資料篩選-2
    參考資料
  • 論壇相關問題討論 123

沒有留言:

張貼留言