星期五, 8月 31, 2018

[SQL] 單一資料表使用多個索引

之前上課時，老師有提到一個 Table 只會使用到一個 Index，沒有想很多就這樣記在腦海裡，最近閱讀效能調校書籍時，提到一個 Table 會用上多個 Index，打破既有的觀念，測試結果也真的有使用多個 Index

[SQL] 單一資料表使用多個索引

上述範例有使用到 IX_LastName、IX_FirstName、IX_MiddleName 這三個 Index 來搜尋資料
    參考資料
  • Microsoft SQL Server Performance Tuning 效能調校 P450 8-2-4 單一查詢使用多個索引
沒有留言:

星期三, 8月 29, 2018

[SQL] 清除指定執行計劃快取

在測試環境常常下 DBCC FREEPROCCACHE 來清除執行計畫，發現 DBCC FREEPROCCACHE 可以輸入 plan_handle 為參數來清除指定執行計畫，並不是一定都要清除全部

執行 TSQL 語法並透過 DMV 找出該執行計畫的 plan_handle
USE AdventrueWorks2016
GO

SELECT * FROM Person.Person
GO

SELECT
  cap.plan_handle ,
  cap.usecounts ,
  cap.cacheobjtype ,
  cap.objtype ,
  st.text
FROM sys.dm_exec_cached_plans AS cap
  CROSS APPLY sys.dm_exec_sql_text(cap.plan_handle) AS st
WHERE st.text NOT LIKE '%sys%'
  AND st.text LIKE N'SELECT * FROM Person.Person%'
GO

[SQL] 清除指定執行計劃快取

清除指定執行計畫
DBCC FREEPROCCACHE(0x06000900FB17592A9082C9015102000001000000000000000000000000000000000000000000000000000000)
沒有留言:

星期三, 8月 22, 2018

[SQL] 避免欄位比較 - 計算欄位

在 [SQL] 避免欄位比較 - 篩選索引 這篇筆記內，是建立一個待運送 (剩餘數量) 欄位來改善，這篇筆記就改為建立 [已運輸數量欄位] 來筆記囉

建立已運輸數量欄位、更新資料並建立 IX_物流申請_已運輸數量 Index
ALTER TABLE 物流申請 ADD 已運輸數量 int NOT NULL DEFAULT(0)
GO

CREATE INDEX IX_物流申請_已運輸數量 ON 物流申請 (已運輸數量)
GO
要在已運送數量欄位形式，去找出待運送數量資訊，就只有下面兩種不符合 SARG 寫法
-- 欄位資料 運算子 欄位資料
SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

-- 對欄位進行運算
SELECT * FROM 物流申請 WHERE 申請運輸數量 - 已運輸數量 > 0
觀察執行計劃就會發現，上述兩種寫法，因為違反 SARG，所以根本就不會使用 IX_物流申請_已運輸數量，通通跑 Clustered Index Scan
強制指定 Index 來看看結果
SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WITH (INDEX(IX_物流申請_已運輸數量)) WHERE 申請運輸數量 > 已運輸數量
結果當然是更慘囉
原本是要避免 [資料欄位 運算子 資料欄位]，這種違反 SARG 的設計，沒想到因為是建立並儲存 [已運送數量] 欄位，仍然是陷入同樣問題，在木已成舟情況下，要使用符合 SARG 語法並找出 [待運送數量] 資料，還能透過 [計算欄位] 來達到

建立計算欄位並建立計算 Index
ALTER TABLE 物流申請 ADD ComputeCol AS (申請運輸數量 - 已運輸數量) PERSISTED 

CREATE INDEX IX_物流單_ComputeCol ON 物流申請 (ComputeCol)
原語法和計算欄位較
SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WHERE ComputeCol > 0
公司生管派工系統，彼此之間會已完工數量來溝通，基本上不會有人跟下製程說，還剩下多少數量沒有完成，而是告知已完成數量，也因此在 Table 設計時，各製程是儲存已完工數量資訊，在判斷製程完工時也會遇上該問題，剛好整理一下，以後這類系統發生問題時，至少有個明確方向可以來改善
沒有留言:

星期二, 8月 21, 2018

[SQL] 避免欄位比較 - 篩選索引

續 [SQL] 避免欄位比較 該篇內容，確定要更改 Table Schema 後，就直接增加一個 [待運輸數量 (剩餘數量)]  欄位來儲存資料並建立 Index 提高效能

建立待運輸數量欄位並把資料更新進去
ALTER TABLE 物流申請 ADD 待運輸數量 int NOT NULL DEFAULT(0)
因為商業邏輯上是待運送數量，運送完後數量會減少，所以預期會有一堆 0 的資料，因此建立篩選索引來過濾掉 0 的資料

利用下述語法，確認資料分布情況
SELECT
  待運輸數量 ,
  COUNT(待運輸數量) AS 待運輸總數量
FROM 物流申請
GROUP BY 待運輸數量
ORDER BY 待運輸總數量 DESC

故意建立一般非叢集索引和篩選索引來比較一下
-- 建立非叢集索引
CREATE INDEX IX_物流申請_待運輸數量 ON 物流申請 (待運輸數量)

-- 建立篩選索引
CREATE INDEX IF_物流申請_待運輸數量 ON 物流申請 (待運輸數量)
WHERE 待運輸數量 > 0
指定索引來測試看看是否有差異
SET STATISTICS IO , TIME ON

-- 強制使用非叢集索引
SELECT * FROM 物流申請 WITH (INDEX(IX_物流申請_待運輸數量)) WHERE 待運輸數量 > 0

-- 強制使用篩選索引
SELECT * FROM 物流申請 WITH (INDEX(IF_物流申請_待運輸數量)) WHERE 待運輸數量 > 0
測試相關資料

非叢集篩選
Logical Read9289
執行計劃成本0.08550.0855
Index 使用方式Index SeekIndex Scan

就效能來說，是幾乎沒有提升，只好查詢一下兩個 Index 資訊，來輔助一下使用篩選索引的決定囉，至少維護索引成本是比較低的，哈
SELECT 
  T2.index_id, 
  T2.name, 
  T2.type_desc, 
  T1.reserved_page_count, 
  T1.used_page_count,
  T1.row_count,
  T2.filter_definition 
FROM sys.dm_db_partition_stats AS T1
  INNER JOIN sys.indexes T2 ON T1.[Object_ID] = T2.[Object_ID] 
                              AND T1.index_id = T2.index_id
WHERE T1.[Object_ID] = OBJECT_ID('物流申請')
  AND T2.[Name] IN ('IX_物流申請_待運輸數量','IF_物流申請_待運輸數量')
最後一步就是 AP 端也改一改，利用一個欄位來儲存資料，就可以避免欄位比較情況
沒有留言:

星期一, 8月 20, 2018

[SQL] 避免欄位比較

閱讀 Microsoft SQL Server Performance Tuning 效能調校的 SARG 介紹時，提到 [資料欄位 運算子 資料欄位] 是不符合 SARG 原則，以往看到的資料大多是強調 [資料欄位 符合 SARG 運算子 <常數或變數>] 符合 SARG，最近調校有剛好發現很多 [資料欄位 運算子 資料欄位] 語法，不太能從 TSQL 語法變化來提升效能，所以重新閱讀書籍內容時特別有感

SARG 意義

在查詢子句中，SARG 代表用來搜尋的常數或變數可以直接與索引鍵值做比較 

SARG 運算子列表

  • 符合：=、<、>、>=、<=、BETWEEN、LIKE (看 % 位置) 
  • 不符合：NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN、NOT LIKE 

SARG 格式理論

  • 符合：
    • 資料欄位 符合 SARG 運算子 <常數或變數>
    • <常數或變數> 符合 SARG 運算子 資料欄位
  • 不符合：資料欄位 運算子 資料欄位

案例說明

物流申請例子來說明，商業邏輯很簡單派車單 (檔頭、檔身) 搭配物流申請，物流單申請可以存在多張多張派車單內，所以要知道待運送的物流申請資料，必須用下述語法去比對出來
SELECT
  T.*
  , D.物流單編號
  , ISNULL(D.已運輸數量, 0) AS 已運輸數量
FROM 物流申請 AS T
  LEFT JOIN 
    (
      SELECT
        物流單編號
        , SUM(已運輸數量) AS 已運輸數量
      FROM 派車檔身
      GROUP BY 物流單編號
    ) AS D ON T.物流單編號 = D.物流單編號
WHERE (D.物流單編號 IS NULL        -- 物流申請還未派車
  OR T.物流申請數量 > D.已運輸數量) -- 物流申請已經派車，但還未完全派車完

當時這樣設計看執行 Statistics 覺得很 OK、還覺得執行計劃有跑出平行處理很不錯，時過境遷後，現在看到平行處理就覺得事情不尋常，Orz

醜醜的執行計畫，物流申請和派車檔身都跑 Clustered Index Scan 來處理

有該篇 - [SQL] 利用 UNION ALL 取代 OR 條件 經驗，當然是要來嘗試一下
-- 物流申請還未派車
SELECT
  T.* ,
  0 AS 已運輸數量
FROM 物流申請 AS T
WHERE NOT EXISTS
  (
    SELECT 1 FROM 派車檔身 AS D WHERE T.物流單編號 = D.物流單編號
  )
UNION ALL
-- 物流申請已經派車，但還未完全派車完
SELECT
  T.*
  , D.已運輸數量
FROM 物流申請 AS T
  JOIN
    (
      SELECT
        物流單編號
        , SUM(已運輸數量) AS 已運輸數量
      FROM 派車檔身
      GROUP BY 物流單編號
    ) AS D ON T.物流單編號 = D.物流單編號
WHERE T.物流申請數量 > D.已運輸數量
改完之後發現更慘，Statistics 為原本兩倍左右，執行計畫也肥了不少

無法從語法獲得改善，就只能動 Table Schema 來改善囉
沒有留言:

星期五, 8月 10, 2018

[SQL] 避免使用不等於

SARG 的一個原則 - 避免使用否定語法，EX：<>，調校時剛好有用上，記錄一下

[F1.工序結案數量 <> S1.派工數量] 是關鍵條件，剛好了解這條件商業邏輯，派工單各工序數量不等於派工單數量情況，換句話說就是，各工序數量小於派工數量的情況，擔心誤會還特定跟同事確認，應該不太可能會有各工序完工數量，超過派工數量的情況才對
SELECT
	..................
FROM
	..................
	JOIN [派工工序 Table] AS F1 ON D.派工單號 = F1.派工單號
			AND D.派工工序 = F1.派工工序
			AND F1.工序結案數量 <> S1.派工數量 -- 關鍵條件
條件修正
---- 原語法
F1.工序結案數量 <> S1.派工數量
----- 了解商業邏輯情況下改為
F1.工序結案數量 < S1.派工數量
----- 完全不懂商業邏輯下會改為
F1.工序結案數量 < S1.派工數量 OR F1.工序結案數量 > S1.派工數量
上述兩種改法都是可以得到相同結果，Statistics 雖然沒有甚麼變化，執行計劃成本降低不少

[SQL] 避免使用不等於
沒有留言:

星期四, 8月 09, 2018

[SQL] 避免 CTE 遞迴產生日期總表

剛學 CTE 時，喜歡用 CTE 來跑日期總表，發現效能實在是很糟糕，後來建立一個實體 Table - DateTable，用來記錄日期資訊，這個算是漏網之魚，^^''

;
WITH CTE
AS
(
   SELECT
     CAST(@P1 AS DATETIME) AS CTEDate UNION ALL SELECT
     DATEADD(DD, 1, CTEDate)
   FROM CTE
   WHERE DATEADD(DD, 1, CTEDate) <= CAST(@P2 AS DATETIME)
)
SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM CTE AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= CTEDate
                             AND N.結案時間 < DATEADD(DD, 1, CTEDate)
          ..................
      ) AS TF
    GROUP BY ..................
  ) AS F
OPTION (MAXRECURSION 0)
Statistics 很糟糕外，執行計畫也跑出平行處理，下圖為部分截圖

[SQL] 避免 CTE 遞迴產生日期總表-1

修正後語法
SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM DateTable AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= T.Date
                             AND N.結案時間 < DATEADD(DD, 1, T.Date)
          ..................
        WHERE T.Date BETWEEN @P1 AND @P2
      ) AS TF
    GROUP BY ..................
  ) AS F

修正前後執行計劃成本差異

  [SQL] 避免 CTE 遞迴產生日期總表-2
修正前後 Statistics 差異

修正前修正後
CPU Time1,562 ms31 ms
WorkTable Logical Read815,4190
沒有留言:

星期三, 8月 08, 2018

[Win10] 使用小型工作列按鈕

同事來詢問，為什摩 Win10 右下角的日期時間，他的 PC 上竟然只有時間，沒有日期，如下圖

[Win10] 使用小型工作列按鈕-1

可是我的右下角有日期時間，兩個資訊都有

[Win10] 使用小型工作列按鈕-3

後來他才發現到他有調整 [使用小型工作列按鈕] 這個選項，所以才沒有顯示日期

[Win10] 使用小型工作列按鈕-2
沒有留言:

星期二, 8月 07, 2018

[SQL] 錯誤 Index 設計

檢視公司系統內的 Index 時，發現這個有趣的錯誤，有趣的點在於，SNO 欄位是叢集索引，建立 Kind 欄位的非叢集索引，本來就會有叢集索引 Key 值，幹嘛還需要弄個覆蓋索引來 include 叢集索引欄位，^^''
沒有留言:

星期一, 8月 06, 2018

[SQL] 覆蓋索引 - 排序維護

跟同事討論覆蓋索引相對於複合索引，筆記中有下面優點，
SQL Server 提供在索引子頁含入與鍵值無關的資料表其他欄位，但這個欄位值不做排序等額外維護動作
文字說明應該是來至 [SQL Server Performance Tuning 效能調校] 書籍，當初筆記時忘記紀錄出處，Orz

討論過程中，發現好像也從來沒有真的去驗證過

利用 AdventureWroks2016CTP3 的 Person.Perosn Table 來觀察
-- 複合索引語法
CREATE INDEX IX_Person_MultiColumnIndex ON Person.Person 
 (LastName,FirstName,MiddleName)

-- 覆蓋索引語法
CREATE INDEX IX_Person_CoveringIndex ON Person.Person (LastName)
INCLUDE(FirstName , MiddleName)

[SQL] 覆蓋索引 - 排序維護
沒有留言:

星期日, 8月 05, 2018

[SQL] UNION ALL 一定會讀出全部資料？

平常跟同事討論時，都會說，SQL Server 是 Cost Base，不是 Rule Base，Query Optimizer 會去找出最低成本執行計畫來執行，所以效能議題，一定要開啟 Statistics IO,Time、執行計畫和執行計畫成本來判斷，不要看 TSQL 語法就在那臆測效能如何，不過講歸講，自己對於使用 UNION ALL 會讀出全部資料後似乎也堅信不移，不知道哪來的觀念，Orz

調校遇上使用到 UNION ALL 語法，調校前 (使用 5 次) 後 (使用 3 次) 都沒有因為 UNION ALL 關係導致讀出全部資料來操作

改善後語法
SELECT
  ..................
FROM
  (
    SELECT
      ..................
    FROM 出貨檔頭
    WHERE
      ..................
    UNION ALL
    SELECT
      ..................
    FROM 出貨檔頭 AS R
      JOIN 
        (
          SELECT
            ..................
          FROM 客戶
        ) AS w1 ON r.客戶編號 = w1.客戶編號
                  AND (r.date BETWEEN w1.起始日期 AND w1.結束日期)
    WHERE
      .................. 
  ) AS T
  JOIN vw客戶 AS c1 ON T.CustNO = c1.CustNO
  JOIN 
  (
    SELECT
      ..................
    FROM 出貨 AS t
    UNION ALL 
    SELECT
      ..................
    FROM 退貨 AS t 
    UNION ALL 
    SELECT
      ..................
    FROM 庫存出貨 AS t
  ) AS p ON T.out_id = p.out_id
  JOIN 訂單 AS F ON P.訂單編號 = F.訂單編號
從下面執行計畫部分截圖，就可以發現啦，是利用 Nested Loops 一筆一筆進 UNION ALL 內的每一個 Table 內把資料找出來，最後再 UNION ALL 後傳出
然後很無聊的把第 25 行的 JOIN 改為 LEFT JOIN，想說會不會因此就讀出全部資料 UNION ALL 後才會去進行比對，執行計劃也沒有變化，^^''

自己的迷思自己來打破，還是要觀察執行計劃才會知道 TSQL 是如何執行的

沒有留言:

星期六, 8月 04, 2018

[SQL] UNION ALL 發生隱含轉換

利用 DMV 語法找到下述 TSQL 有隱含轉換存在
SELECT
  ........................
FROM [物料設計BOM表] AS w
  JOIN [物料View] AS m
WHERE
  ........................
  AND m.物料隱藏 = 0
[SQL] UNION ALL 發生隱含轉換-1

因為 Hide 欄位資料型態是 bit，想說把 0 改為 CAST(0 as bit) 就可以結案，沒想到執行計畫竟然還是有隱含轉換
SELECT
  ........................
FROM [物料設計BOM表] AS w
  JOIN [物料View] AS m
WHERE
  ........................
  AND m.物料隱藏 = CAST(0 AS bit)
最後發現是 View 內老早就隱含轉換了，Orz
SELECT
  .................. ,
  m.物料隱藏 -- Table 內真實存在欄位，資料型態為 bit
FROM 物料表 AS m
UNION ALL
SELECT
  .................. ,
  0 -- 配合 UNION ALL 的虛擬欄位
FROM 客戶物料表
UNION ALL
SELECT
  .................. ,
  0 -- 配合 UNION ALL 的虛擬欄位
FROM 客戶規格表
UNION ALL
SELECT
  .................. , 
  0 -- 配合 UNION ALL 的虛擬欄位
FROM 設計變更表
UNION ALL
SELECT
  .................. ,
  0 -- 配合 UNION ALL 的虛擬欄位
FROM 派工單檔頭
WHERE
  ..................
從 資料類型優先順序 文章內就可以查到，int 優先權是高於 bit，所以 View 內的 [物料隱藏] 欄位就被轉為 int 了，把 View 內的 0 通通改為 CAST(0 as bit)，執行計畫中隱含轉換字樣終於消失了

[SQL] UNION ALL 發生隱含轉換-2

以前看見的網路文章都是在 WHERE 條件內，判斷等於時出現的，沒想到 UNION ALL 也會存在隱含轉換
沒有留言:

星期五, 8月 03, 2018

[SQL] 利用 UNION ALL 取代 OR 條件

在調校這篇 [SQL] WHERE 條件合理性 時，其實一開始的想法是要用 UNION ALL 來把兩個條件拆開，只不過後來從參數值發現是個烏龍，筆記一下這個未採用作法，其實也是會有所改善

原語法
SELECT
  S.派工單號 ,
  I.訂單單號
FROM 派工單Table AS S
  LEFT JOIN 訂單明細Table AS I ON S.派工單號 = I.派工單號
WHERE S.派工單號 = @P1
  OR I.訂單單號 = @P2
[SQL] 利用 UNION ALL 取代 OR 條件-1 
UNION ALL 取代 OR 條件
SELECT
  S.派工單號 ,
  I.訂單單號
FROM 派工單Table AS S
  LEFT JOIN 訂單明細Table AS I ON S.派工單號 = I.派工單號
WHERE S.派工單號 = @P1
UNION ALL
SELECT
  S.派工單號 ,
  I.訂單單號
FROM 派工單Table AS S
  JOIN 訂單明細Table AS I ON S.派工單號 = I.派工單號
WHERE I.訂單單號 = @P2
[SQL] 利用 UNION ALL 取代 OR 條件-2
執行計畫成本比較

[SQL] 利用 UNION ALL 取代 OR 條件-3
沒有留言:

星期四, 8月 02, 2018

[SQL] WHERE 條件合理性

利用 DMV 語法抓出有發生平行處理執行計劃時發現下述語法
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1
  OR i.odno = @P2
[SQL] WHERE 條件合理性-1

從 DMV 抓出傳入的參數值是派工單號，然後更神奇的事情是，Odno 欄位是訂單編號，永遠不會成立的 WHERE 條件，Orz

修正 TSQL，把 Odno 條件拿掉
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1
[SQL] WHERE 條件合理性-2
執行計畫比較

[SQL] WHERE 條件合理性-3
沒有留言:

星期三, 8月 01, 2018

[SQL] 避免 UNION

之前學習 UNION 時，有提到 UNION 和 UNION ALL 在效能上的差異，剛好遇上調校 TSQL 語法時，有發現使用到 UNION，改成 UNION ALL 後，有改善就好，哈

基本上執行計畫中只少掉 Sort 操作子相關

[SQL] 避免 UNION-執行計畫比較

執行計畫成本從 5.76 => 5.75，只少掉 0.01，^^''

[SQL] 避免 UNION-執行計畫成本比較

可以參考這兩篇，效能大幅改善例子
沒有留言: