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

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

建立 LeftQty 欄位並把資料更新進去

ALTER TABLE Transport ADD LeftQty int NOT NULL DEFAULT(0)

因為商業邏輯是待運送數量，所以預期會有一堆 0 的資料，因此想建立篩選索引來過濾掉 0 的資料

利用下述語法，確認資料分布情況

SELECT
  LeftQty ,
  COUNT(LeftQty) AS Total
FROM Transport
GROUP BY LeftQty
ORDER BY Total DESC

故意建立一般非叢集索引和篩選索引來比較一下

-- 建立非叢集索引
CREATE INDEX IX_Transport_LeftQty ON Transport (LeftQty)
-- 建立篩選索引
CREATE INDEX IF_Transport_LeftQty ON Transport (LeftQty)
WHERE LeftQty > 0

指定索引來測試看看是否有差異

SET STATISTICS IO , TIME ON
-- 強制使用非叢集索引
SELECT * FROM Transport WITH (INDEX(IX_Transport_LeftQty)) WHERE LeftQty > 0
-- 強制使用篩選索引
SELECT * FROM Transport WITH (INDEX(IF_Transport_LeftQty)) WHERE LeftQty > 0

測試相關資料

	非叢集	篩選
Logical Read	92	89
執行計劃成本	0.0855	0.0855
Index 使用方式	Index Seek	Index 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('Transport')
  AND T2.[Name] IN ('IX_Transport_LeftQty','IF_Transport_LeftQty')

最後一步就是 AP 端也改一改，利用一個欄位來儲存資料，就可以避免欄位比較情況

參考資料
• [SQL SERVER][Memo]篩選索引
• [SQL SERVER][Memo]篩選索引(2)