[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 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('物流申請')
  AND T2.[Name] IN ('IX_物流申請_待運輸數量','IF_物流申請_待運輸數量')

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

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