我們在取 Workflow 的待辦事項時，會從 Work 的待辦資料中，依這個使用者的代號、部門主管、角色或是部門 + 角色的條件來取得這個人的待辦資料。
所以整個 SQL 中 Where 條件中會有很多的 OR，像這種很多 OR 的查詢效能要如何解決呢?
整個 SQL 類似如下，
SELECT …..
FROM WORKLIST_IV WITH ( READPAST )
WHERE ( ( PTCP_KIND = N'1'
AND PTCP_COMP_ID = N’655’
AND PTCP_USR = N'S122484051'
)
OR ( ( PTCP_KIND = N'3'
AND PTCP_DOMAIN_ID = N'GSS'
AND PTCP_COMP_ID = N’655’
)
AND ( (PTCP_OU = N'379132200C2202'
AND PTCP_RELKIND = N'1')
)
)
OR ( ( PTCP_KIND = N'2'
AND PTCP_DOMAIN_ID = N'GSS'
AND PTCP_COMP_ID = N’655’
)
AND ( ( PTCP_ROL = N'ODDeskUsr_Rol_4'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_ROL = N'ODDeskUsr_Rol_4_379132200C2202'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_ROL = N'SWS_OuExamRol'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_ROL = N'SWS_OuExamRol_379132200C22AA'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_ROL = N'SWSProcesser_Rol'
AND PTCP_RELKIND = N'1'
)
)
)
OR ( ( PTCP_KIND = N'5'
AND PTCP_DOMAIN_ID = N'GSS'
AND PTCP_COMP_ID = N’655’
)
AND ( ( PTCP_OU = N'379132200C2202'
AND PTCP_ROL = N'ODDeskUsr_Rol_4'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_OU = N'379132200C2202'
AND PTCP_ROL = N'ODDeskUsr_Rol_4_379132200C2202'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_OU = N'379132200C2202'
AND PTCP_ROL = N'SWS_OuExamRol'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_OU = N'379132200C2202'
AND PTCP_ROL = N'SWS_OuExamRol_379132200C22AA'
AND PTCP_RELKIND = N'1'
)
OR ( PTCP_OU = N'379132200C2202'
AND PTCP_ROL = N'SWSProcesser_Rol'
AND PTCP_RELKIND = N'1'
)
)
)
);
執行計畫如下，
疑，這麼多的 OR，依以往的經驗，就將 Where 都加到 index 之中，如下，
CREATE NONCLUSTERED INDEX IDX_WKITEM_PTCP_I_RM
ON [dbo].[WKITEM_PTCP_I] ([PTCP_COMP_ID],[PTCP_KIND],[PTCP_USR],[PTCP_ROL],[PTCP_ACTN_STATE])
INCLUDE ([WKITEM_ID],[PTCP_ID],[PTCP_NAME],[PTCP_DESC],[PTCP_DOMAIN_ID],[PTCP_OU],[PTCP_GRP],[PTCP_RELKIND],[PTCP_PRO_STATE],[PTCP_RECV_TIME],[PTCP_COMM])
結果執行計畫好不到那裡去，如下，
-- 請先將前面建立的這個 index drop掉哦!
那是不是不要拆 SQL 的寫法，分別針對 OR 去用各別的 SQL ，再 union 起來呢?
後來公司的天空大大，分別建立了3個 index 後就成功化解了這個一次會先將一堆資料取出來後，再透過 篩選 出資料的問題。以下筆者就來分享 Sky 成功化解這個 Where 很多 OR 的問題。
先依每個 OR 中，有相同欄位的部份來建立各別的 index ，如下，
所以我們可以建立 4 個 index, 如下，
CREATE INDEX IX_WKITEM_PTCP_USR ON WKITEM_PTCP_I(PTCP_KIND, PTCP_USR, PTCP_COMP_ID)
CREATE INDEX IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND)
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND)
CREATE INDEX IX_WKITEM_PTCP_OU_2 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL)
建立完成後，再執行SQL，看一下執行計畫為何，如下，
成功的化解掉先取出一堆資料後再「篩選」的問題，只是有些 index 會有 索引鍵查閱（key lookup）。
要解 索引鍵查閱（key lookup）的問題，我們可以使用 Include Index 。所以我們可以查看原本那些 OR 中，那些非相同的欄位，將它們加到 Include Index 之中，如下，
所以 index 改成如下，
--其他的 OR 還有其他的 WHERE 條件，也放在 INCLUDE 之中
CREATE INDEX IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND)
INCLUDE (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND)
INCLUDE (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_OU_2 ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL)
INCLUDE ( PTCP_DOMAIN_ID, PTCP_COMP_ID);
執行計畫如下，
這樣我們就解掉了 索引鍵查閱（key lookup）的問題，但執行計畫之前可以發現，index 並沒有用到 IX_WKITEM_PTCP_OU_1 ，只有用到了 IX_WKITEM_PTCP_OU_2。
所以我們可以將 IX_WKITEM_PTCP_OU_1 刪掉，並將 IX_WKITEM_PTCP_OU_2 改成 IX_WKITEM_PTCP_OU，如下，
drop index IX_WKITEM_PTCP_OU_1 ON WKITEM_PTCP_I;
EXEC sp_rename N'WKITEM_PTCP_I.IX_WKITEM_PTCP_OU_2', N'IX_WKITEM_PTCP_OU', N'INDEX';
再看一下執行計畫， IX_WKITEM_PTCP_OU_2 的名稱就改成了 IX_WKITEM_PTCP_OU，如下，
如果加入 include index 欄位後，還是有 key lookup 的話，可以再看一下那個 View 中那個 Table 的 Join Key 哦!
註:本篇是依 天空大大建議的 index , 筆者依公司DB狀況測試調整，各位調整時也請依實際環境狀況調整哦!
所以最後新增的index 有3個，如下，
CREATE INDEX IX_WKITEM_PTCP_USR ON WKITEM_PTCP_I(PTCP_KIND, PTCP_USR, PTCP_COMP_ID)
--其他的 OR 還有其他的 WHERE 條件，也放在 INCLUDE 之中
CREATE INDEX IX_WKITEM_PTCP_ROL ON WKITEM_PTCP_I(PTCP_KIND, PTCP_ROL, PTCP_RELKIND)
INCLUDE (PTCP_DOMAIN_ID, PTCP_COMP_ID);
CREATE INDEX IX_WKITEM_PTCP_OU ON WKITEM_PTCP_I(PTCP_KIND, PTCP_OU, PTCP_RELKIND, PTCP_ROL)
INCLUDE (PTCP_DOMAIN_ID, PTCP_COMP_ID);
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
