尋求幫助優化自聯接

由於末尾的 WHERE IN() 語句，此查詢花費的時間比應有的長約 5 倍。我是一名初級 DBA，正在努力尋找優化它的方法。有沒有辦法檢查 IN() 外部查詢的結果集？可以組合任何自聯接嗎？有沒有推薦的查詢優化資源？

為缺乏極簡主義、完整性和可驗證性而道歉——包括 DDL 似乎有點矯枉過正。下面是查詢和執行計劃的瓶頸。

SELECT SC.ROW_ID      C1_CASE_ID,
      SC2.ROW_ID     C2_NEW_CASE_ID,
      SC2.CASE_NUM   C3_NEW_CASE_NUM,
      MC.ROW_ID      C4_MSTR_CASE_ID,
      MC2.ROW_ID     C5_NEW_MSTR_CASE_ID,
      MC2.CASE_NUM   C6_NEW_MSTR_CASE_NUM
FROM  siebel.ODS_S_CONTACT          MCON2, 
      siebel.ODS_S_CONTACT          MCON, 
      siebel.ODS_S_CASE             SC, 
      siebel.ODS_S_CASE             MC, 
      siebel.ODS_S_CASE_BNFTPLAN    CBP, 
      siebel.ODS_S_CASE             MC2, 
      siebel.ODS_S_CASE             SC2
WHERE (SC.STATUS_CD = 'Withdrawn')
 AND (CBP.STATUS_CD IN ('Active', 'Approved','Inactive') 
 AND EXISTS (
              SELECT 1 
                FROM (SELECT cbp2.row_id cbp2_row_id, 
                             cbp2.case_id cbp2_case_id,
                             DENSE_RANK() OVER (PARTITION BY cbp2.case_id ORDER BY CASE WHEN cbp2.status_cd = 'Active' THEN 1
                                                                                        WHEN cbp2.status_cd = 'Approved' THEN 2
                                                                                        WHEN cbp2.status_cd = 'Inactive' THEN 3
                                                                                        ELSE 4 
                                                                                   END,cbp2.created DESC
                                               ) cbp2_order
                        FROM siebel.ods_s_case_bnftplan cbp2
                     --where cbp2.case_id = CBP.CASE_ID
                     ) sq
               WHERE cbp2_order = 1
                 AND CBP.CASE_ID = cbp2_case_id
                 AND CBP.ROW_ID = cbp2_row_id
            )
     )
WHERE (SC.MSTR_CASE_ID=MC.ROW_ID)
 AND (MC.APPLICANT_ID=MCON.ROW_ID)
 AND (MCON.SOC_SECURITY_NUM=MCON2.SOC_SECURITY_NUM AND MCON.ROW_ID <> MCON2.ROW_ID)
 AND (MCON2.ROW_ID=MC2.APPLICANT_ID)
 AND (MC2.ROW_ID=SC2.MSTR_CASE_ID AND SC2.TYPE_CD = SC.TYPE_CD AND SC2.STATUS_CD = 'Active')
 AND (SC.ROW_ID=CBP.CASE_ID)
 AND SC2.CREATED IN (
                       -- default: 46 rows  ~30seconds
                       SELECT MAX(SCSUB.CREATED)
                         FROM siebel.ODS_S_CASE          SC2SUB
                        INNER JOIN siebel.ODS_S_CASE    MC2SUB        ON MC2SUB.ROW_ID = SC2SUB.MSTR_CASE_ID
                        INNER JOIN siebel.ODS_S_CONTACT MCON2SUB      ON MC2SUB.APPLICANT_ID = MCON2SUB.ROW_ID
                        INNER JOIN siebel.ODS_S_CONTACT MCONSUB       ON MCON2SUB.SOC_SECURITY_NUM = MCONSUB.SOC_SECURITY_NUM
                        INNER JOIN siebel.ODS_S_CASE    MCSUB         ON MCONSUB.ROW_ID = MCSUB.APPLICANT_ID
                        INNER JOIN siebel.ODS_S_CASE    SCSUB         ON MCSUB.ROW_ID = SCSUB.MSTR_CASE_ID 
                        INNER JOIN siebel.ODS_S_CASE_BNFTPLAN CBPSUB  ON CBPSUB.CASE_ID = SCSUB.ROW_ID
                        WHERE SC2SUB.ROW_ID = SC2.ROW_ID
                          AND  CBPSUB.STATUS_CD IN ('Active', 'Approved')
                          AND SC2SUB.TYPE_CD = SCSUB.TYPE_CD 
                          AND SCSUB.STATUS_CD = 'Active'
                     )

執行計劃在這裡。

看起來你那裡的索引很少。如果這不是第三方應用程序，那麼這裡有一些我會測試的索引。我在做一些假設，比如 Row_ID 是一個唯一的列。

CREATE UNIQUE CLUSTERED INDEX CIX_OdsSCase_Sc2Sub_RowId ON ODS_S_Case.Sc2Sub
(
   RowId
)

CREATE INDEX IX_OdsSContact_Mcon2Sub_SocSecurityNum ON ODS_S_Contact.MCON2SUB
(
   Soc_Security_Num
)
INCLUDE
(
   Row_Id
)

CREATE INDEX IX_OdsSCaseBnftplan_Cbpsub_CaseId ON ODS_S_CASE_BNFTPLAN
(
   CASE_ID
)
INCLUDE
(
   STATUS_CD
)

CREATE INDEX IX_OdsSContact_Mconsub_SocSecurityNum ON ODS_S_CONTACT.MCONSUB
(
   Soc_Security_Num
)
INCLUDE
(
   Row_Id
)

CREATE INDEX IX_OdsSCase_Scsub_StatusCd_MstrCaseId ON ODS_S_CASE.SCSUB
(
   Status_Cd
   , Mstr_Case_Id
)
INCLUDE
(
   Row_Id
   , Created
   , Type_Cd
)

另外，我不能不說什麼就看到這一點。您似乎有一個未加密的社會安全號碼，用作外鍵，假設它是唯一的，等等。這在許多層面上都是一個風險。如果我看到的是正確的並且您不相信這是一個壞主意，您應該閱讀https://www.computerworld.com/article/2552992/not-so-unique.html和https://helifromfinland.blog /2014/04/18/is-social-security-number-a-good-primary-key/

我現在在這上面花了太多時間。在您的測試環境中試用這些索引，看看它有多大的不同。如果您能夠進行這些類型的更改並且不知道您應該開始尋找向人們介紹索引的部落格文章。

IMO，您的查詢根本不是優化。它不適合索引調整。

可能有些索引會給你暫時的緩解。

此外，由於缺乏表格設計、數據和要求，無法重寫您的查詢。

所以我的查詢只是為了給你一個想法，但我對我的想法有 100% 的把握。

我的建議，

1. 明確地Join在查詢中使用Readability。

很難捕捉到哪個表與哪個表和哪個列連接。

1. 用於 Temp table儲存重複的結果集。如果我知道所有需要的資訊，那麼建議您使用CTE.

這種方式優化器必須一次又一次地處理有限的結果集。

就像，您可以將表中的值siebel.ODS_S_CASE_BNFTPLAN 與#BNFTPLAN此過濾器一起放入。

   CREATE table #BNFTPLAN(case_id int ,row_id int,status_cd int)

--Put  all columns of ODS_S_CASE_BNFTPLAN in this temp table that will be use in query along with status_cd as int

Insert into #BNFTPLAN(status_cd)
select 
WHEN cbp2.status_cd = 'Active' THEN 1    WHEN cbp2.status_cd = 'Approved' THEN 2
                                                WHEN cbp2.status_cd = 'Inactive' THEN 3
                                               end 
from siebel.ODS_S_CASE_BNFTPLAN
where CBP.STATUS_CD IN ('Active', 'Approved','Inactive') 

-- Do not write DENSE_RANK logic in #BNFTPLAN
-- Notice I have omitted `ELSE 4`  from case because that is not required .
-- Because of this `where cbp2.case_id = CBP.CASE_ID`

不要忘記閱讀我的評論並記住我的查詢只是粗略的工作，你必須完美地實現它。

將值 siebel.ODS_S_CASE放入#ODS_S_CASE表中

CREATE table #ODS_S_CASE(row_id int,CASE_NUM int,APPLICANT_ID int,TYPE_CD int,CREATED datetime)

-- In ODS_S_CASE carefully declare all columns which will be required in this query.

insert into #ODS_S_CASE(row_id,CASE_NUM,APPLICANT_ID,TYPE_CD,CREATED)
select row_id,CASE_NUM,APPLICANT_ID,TYPE_CD,CREATED from siebel.ODS_S_CASE sc
inner join #BNFTPLAN CBP
       on SC.ROW_ID=CBP.CASE_ID
--inner join MCON.ROW_ID
       where SC.STATUS_CD = 'Active'

請注意我如何放置 where 條件SC.STATUS_CD = 'Active'並加入#BNFTPLAN CBP並嘗試使用MCON.ROW_ID

在臨時表中插入時仔細應用 where 條件和連接將限制結果集。因此優化器不會一次又一次地處理大量行。

這將提供準確Cardianility Estimate的幫助優化器製定正確的計劃。

這將使您的查詢性能提高約 3 倍或約 5 倍。

此時，您可以將查詢顯示為索引調整或進一步改進。

引用自：https://dba.stackexchange.com/questions/231080