[SQL] 避免欄位相加進行比對

觀察 TOP 10 CPU 時間 DMV 語法時，發現下面語法跑了 200 多次且 CPU Time 都超過 1 秒，Orz

原 TSQL 語法，是把欄位利用 + 串接在一起後，來進行資料比對，這一看就知道一定會 GG 的

INSERT INTO 工單領料順序 (................)
  SELECT
    .............................
  FROM 派工工序 AS F
    JOIN 派工單 AS S ON F.派工單號 = S.派工單號
  WHERE
    AND .............................
    AND F.派工單 + F.派工工序 NOT IN 
      ( 
        SELECT
          派工單號 + 派工工序
        FROM 工單領料順序
      )

改善後 TSQL 語法，改寫為利用 NOT EXISTS 來判斷資料是否存在

INSERT INTO 工單領料順序 (................)
  SELECT
    .............................
  FROM 派工工序 AS F
    JOIN 派工單 AS S ON F.派工單號 = S.派工單號
  WHERE
    AND .............................
    AND NOT EXISTS
      (
        SELECT 1
        FROM 工單領料順序 AS M 
        WHERE F.派工單號 = M.派工單號
          AND F.派工工序 = M.派工工序
      )

測試環境效能資料，只用 insert 一筆資料來測試而已喔

	修正前	修正後
CPU Time	875 ms	0 ms
Logical Read	36,833	279

• 參考資料
• [SQL][內部訓練]如何找出 Master-Detail 中無法匹配的資料

[SQL] 避免 Group By 跨資料表欄位

[SQL] 限制資料筆數 續篇，解決資料大量輸出後，發現有 Group By 跨資料表欄位情況，白話說就是 JOIN Table 之後才對多個 Table 欄位進行彙總

改善前 TSQL 如下

SELECT
  .................
FROM 派工工序 AS F
  .................
  LEFT JOIN 
    (
      SELECT
        KK.派工單號 ,
        KK.工序 ,
        KK.送達與否 ,
        O.部門名稱 ,
        MAX(KK.預定日期) AS 預定日期 ,
        MAX(KK.預約者) AS 預約者 ,
        MAX(KK.預約時段) AS 預約時段 ,
        MAX(KK.優先順序) AS 優先順序 ,
      FROM 物料預約 AS KK
        JOIN 部門 AS O ON KK.處理部門編號 = O.部門編號
      GROUP BY KK.派工單號 , KK.工序 , KK.送達與否 , O.部門名稱
    ) AS K ON K.派工單號 = F.派工單號
             AND K.工序 = F.工序
  .................  
WHERE F.部門編號 = 'D203'
  AND F.已完工數量 < S.派工數量
ORDER BY
  .................

把 TSQL 語法改為 GROUP BY 彙總後再 JOIN Table

SELECT
    T.* ,
    O.部門名稱
FROM
  (
    SELECT
      KK.派工單號 ,
      KK.工序 ,
      KK.送達與否 ,
      KK.處理編號 ,
      MAX(KK.預定日期) AS 預定日期 ,
      MAX(KK.預約者) AS 預約者 ,
      MAX(KK.預約時段) AS 預約時段 ,
      MAX(KK.優先順序) AS 優先順序
    FROM 物料預約 AS KK
    GROUP BY KK.派工單號 , KK.工序 , KK.送達與否 , KK.處理部門編號
  ) AS T
  JOIN 部門 AS O ON T.處理部門編號 = O.部門編號

改善前執行計畫

改善後執行計畫，Sort 操作子還在，有點礙眼，Orz

執行計畫比較

估計的子樹成本比較

TSQL 執行的 Statistics 是沒有甚麼改善，改善後執行計畫看起來還是很糟糕，但執行計畫成本改善不少

[SQL] 日期轉換效能

查 Top 10 語法時，發現 AVG_Logical_Read 極端高的語法，神奇的是從 SQL Server 2005 DMV 抓到的執行計畫，執行計畫中的每個 Table 都用 Index Seek 只輸出一筆資料，完全任何頭緒可以調校，Orz

部分截圖記錄一下

在 SSMS V17.7 上執行該語法，發現最後的 Select 操作子有驚嘆號，一查詢才發現，老朋友又是你 - [SQL] 當天午夜時間

TSQL 關鍵語法

WHERE l.工作日結束日期 > CAST(DATEDIFF(dd, 7, GETDATE()) AS DATETIME)

把 CAST(DATEDIFF(dd, 7, GETDATE()) AS DATETIME) 修正為 CONVERT(char(10),DATEADD(dd,-7,GETDATE()),120)，執行計畫成本馬上出現差異

CAST(DATEDIFF(dd, 7, GETDATE()) AS DATETIME) 的操作子明細

在 Local 端觀察 Statistics 是沒有甚麼異樣，跟同事討論後再修正 TSQL 語法後，再觀察囉

[SQL] 限制資料筆數

收到 Block 超過 1 min 的 Mail 通知，發現下述語法，在 SSMS 上執行該語法，TSQL 執行的 Statistics 完全正常，透過執行計畫才發現異常之處

26 - 33 行語法，同事擔心抓出 PurchDetail 讀出太多資料，因此希望透過 PMSetOrder.Done 來限制過多資料被讀出

SELECT
  ..............

  CASE
    WHEN F.NO <> '' THEN F.NO
    ELSE ISNULL(p.purno, SPACE(11))
  END purno ,
  
  ..............

FROM pmfinishdate AS F
  LEFT JOIN PMMTINOUTPLAN AS MTP ON F.ORDER_NO = MTP.ORDER_NO
                                    AND F.PID = MTP.PID
  LEFT JOIN PRIVATE.DBO.EMPLOY AS EE ON EE.EMPNO = MTP.EMPNO
  LEFT JOIN 
  (
    ......................
  ) AS K ON K.ORDER_NO = F.ORDER_NO
            AND K.PID = F.PID
  JOIN 
  (
    ........................
  ) AS s ON s.order_no = F.order_no
  LEFT JOIN 
  (
    SELECT
      p1.order_no , 
      MIN(p1.purno) AS purno ,
      p1.pid
    FROM ic.dbo.purchdetail AS p1
      JOIN pmsetorder AS s1 ON p1.order_no = s1.order_no
                              AND s1.done = ''
    GROUP BY p1.order_no,p1.pid
  ) AS p ON p.order_no = f.order_no
            AND P.PID = F.PID
  LEFT JOIN 
  (
    .................................
  ) AS W ON F.ORDER_NO = W.ORDER_NO
            AND F.PID = W.PID
            AND F.WK_ID = W.WK_ID
  LEFT JOIN 
  (
    .................................
  ) AS x ON f.order_no = x.order_no
            AND f.pid = x.pid
  LEFT JOIN pmdepworkplan AS wp ON f.order_no = wp.order_no
                                   AND F.PID = WP.PID
                                   AND wp.wk_id = 'D203'
WHERE F.wk_id = 'D203'
  AND F.DONEQTY < S.QTY
ORDER BY F.F_DATE, F.TZ

透過執行計畫，可以發現 PMSetOrder.Done 限制資料是完全沒有發揮作用，反而導致 PurchDetail 利用非叢集索引 - IM_PurchDetail_OrderNO 跑 index scan 把全部資料讀出

把 26 - 33 語法修正，不在需要 PMSetOrder.Done 來限制資料

SELECT
  order_no , 
  MIN(purno) AS purno ,
  pid
FROM ic.dbo.purchdetail
GROUP BY order_no , pid

再次執行就可以發現 PurchDetail 利用 IM_PurchDetail_OrderNO 跑 Index Seek 就結束啦，而且還沒有資料輸出，Orz

改善前後 TSQL 語法成本比較，確實有改善

Statistics 時間比較

	修正前	修正後
CPU 時間	203ms	63ms
經過時間	349ms	287ms

[SSMS] 必須宣告純量變數 "@ServiceStartMode"

使用 SSMS V17.3 連公司內部的 MS SQL Server 2005，一直出現下面的錯誤訊息

一直以為是自己權限設定有問題導致，再加上使用上也沒有出大問題，直到最近突然要操作 SQL Server Agent 時，才發現竟然沒有它，Orz

Google 到該篇 SSMS 的版本更新文章，發現該 Bug 已經在 V17.5 修正啦

修正 JobServer 類別中 SMO 無法提取 SQL 2005 伺服器上 SQL Agent 屬性的問題 (因此，SSMS 將會擲回下列這類錯誤：「無法將預設值指派給區域變數。 必須宣告純量變數 "@ServiceStartMode"，且最後不會在物件總管中顯示 SQL Agent 節點)。

把 SSMS 更新到最新版本，結案，難得有踩到 Bug

[Win10] 修正在 Windows 10 中看起來模糊的應用程式

使用者回報，為什麼公司 ERP 上的字最近變得模糊不清楚，還特地開一個 Excel 來比對，不過很遺憾這類字型清晰度問題，我從來都分不出來，Orz

Windows 10 1803 有新增一個功能 - 修正在 Windows 10 中看起來模糊的應用程式，開啟該功能後，使用者說有改善，那就這樣結案吧，趕快手刀逃離這個無解的個人感受問題，>.<

 

參考資料
• 修正在 Windows 10 中看起來模糊的應用程式