星期三, 8月 22, 2018

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

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

建立 DoneQty 欄位、更新資料並建立 IX_Transport_DoneQty Index
ALTER TABLE Transport ADD DoneQty int NOT NULL DEFAULT(0)
CREATE INDEX IX_Transport_DoneQty ON Transport (DoneQty)
要在已運送數量欄位形式,去找出待運送數量資訊,就只有下面兩種不符合 SARG 寫法
-- 欄位資料 運算子 欄位資料
SELECT * FROM Transport WHERE Qty > DoneQty
-- 對欄位進行運算
SELECT * FROM Transport WHERE Qty - DoneQty > 0
觀察執行計劃就會發現,上述兩種寫法,因為違反 SARG,所以根本就不會使用 IX_Transport_DoneQty,通通跑 Clustered Index Scan

[SQL] 利用計算欄位來避免欄位比較-1

強制指定 Index 來看看結果
SELECT * FROM Transport WHERE Qty > DoneQty
SELECT * FROM Transport WITH (INDEX(IX_Transport_DoneQty)) WHERE Qty > DoneQty
結果當然是更慘囉

[SQL] 利用計算欄位來避免欄位比較-2

原本是要避免 [資料欄位 運算子 資料欄位],這種違反 SARG 的設計,沒想到因為是建立並儲存 [已運送數量] 欄位,仍然是陷入同樣問題,在木已成舟情況下,要使用符合 SARG 語法並找出 [待運送數量] 資料,還能透過 [計算欄位] 來達到

建立計算欄位並建立計算 Index
ALTER TABLE Transport ADD ComputeCol AS (Qty -DoneQty) PERSISTED 
CREATE INDEX IX_Transport_ComputeCol ON Transport (ComputeCol)
原語法和計算欄位較
SELECT * FROM Transport WHERE Qty > DoneQty
SELECT * FROM Transport WHERE ComputeCol > 0
[SQL] 利用計算欄位來避免欄位比較-3

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

沒有留言:

張貼留言