[SQL] 當天午夜時間

同事告知有段 Logical Read 很高的 T-SQL 語法，開啟 [包含實際執行計畫] 和 [SET STATISTICS] 來觀察 T-SQL 執行計畫、Logical Read 和 CPU Time

-- Ctrl + M 開啟執行計畫
SET STATISTICS IO ON
SET STATISTICS TIME ON

原語法示意

SELECT 
  欄位名稱
FROM 訂單檔頭 AS B
  JOIN 客戶 AS C
  JOIN 訂單檔身 AS I
  JOIN 派工工序 AS F 
WHERE B.客戶編號 <> '09084' 
  AND I.剩餘數量 > 0 
  AND I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 
  AND I.派工單號 <> ''
  AND F.工序 = 'A1'
  AND F.完工數量 > 0

重點 Table Logical Read 和 CPU Time 統計資訊

資料表 "訂單檔身" 邏輯讀取 354137。

SQL Server 執行次數:
CPU 時間 = 639 ms，經過時間 = 734 ms。

問題點觀察分析

 

從 Logical Read 可以發現問題在 [訂單檔身] 資料表上，再觀察執行計畫，[訂單檔身] 資料表因為 I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 條件而使用 [IX_訂單檔身_完工日期] 索引，利用 Index Seek 抓出幾乎全部的資料（抓取資料／ 全部資料：117826／118186），檢查索引破碎和統計資訊老舊是否為導致 Query Optimizer（簡稱 QO），誤用索引原因，發現兩者都在合理範圍。

 
I.完工日期 < cast(datediff(DD,0,getdate()) AS datetime) 條件，是要找出今天午夜時間前的資料，完全無法理解 cast(datediff(DD,0,getdate()) AS datetime) 語法邏輯是甚麼，但確定結果正確的，因此特地找執行計畫來了解 QO 到底是怎麼執行的。

 

從執行計畫就可以很清楚知道這段 CAST(DATEDIFF(DD,0,GETDATE()) AS datetime) 是這樣 CONVERT(datetime , DATEDIFF(DD,0,GETDATE()) , 0) 執行的。

尋找午夜時間的方法

-- 方法一：搭配 DATEADD、DATEDIFF 應用
SELECT
 DATEADD
    (dd,
        DATEDIFF(dd,'',GETDATE())
    ,'')
 
-- 方法二：利用轉換資料形態
SELECT
 CAST
    (
      CONVERT(char(10),GETDATE(),120) -- 把 GETDATE() 轉為字串，因限定字元因素，只會產生 yyyy-mm-dd
      AS Datetime
    ) -- 再把 yyyy-mm-dd 轉為 datetime 變成午夜時間

-- 方法三：這次發現的語法
SELECT 
  CAST
     (
       DATEDIFF(DD,0,GETDATE()) AS datetime
     )

原語法跑這三種午夜方法，可以觀察發現，

1. [方法一] 和 [方法三] 會導致 QO 誤用 [IX_訂單檔身_完成日期] 索引
2. [方法二]，QO 則是根據想法使用 [IX_訂單檔身_剩餘數量] 索引來處理

• 方法二

AND I.完工日期 < CAST(CONVERT(char(10),GETDATE(),112) AS datetime)

資料表 "訂單檔身"。掃描計數 1，邏輯讀取 2064。

SQL Server 執行次數:
CPU 時間 = 16 ms，經過時間 = 125 ms。

 

QO 使用 [IX_訂單檔身_剩餘數量] 索引，只抓取 [訂單檔身] 資料表 668 筆資料來處理

 

 

效能比較

訂單檔身 觀察	方法三	方法二
資料筆數	118,186	668
Logical Read	354,137	2,064
CPU Time	639 ms	16 ms

很意外午夜時間竟然會是導致 QO 誤判的問題點，QO 果然是被蠱惑的。

20220415 補述

該筆記是在 SQL Server 2005 上案例，發現午夜時間問題點在 SQL Server 2019 上已經不存在囉，而且重新閱讀該筆記也發現，改善後執行計畫是從 Constant Scan 開始，其實也是蠻奇怪的，該語法在當下應該也不太穩定才是

• 延伸閱讀
• [SQL] 日期 - 基本應用
• [SQL] Like - Constant Scan