4

This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the resultset of the outer query? Can any of the self-joins be combined? Are there any query optimization resources you would recommend?

Apologies for the lack of minimalism, completeness, and verifiyibility - including the DDL seemed like overkill. Below are the query and the execution plan bottleneck.

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'
                      )

Execution Plan here.

Execution Plan

  • @TonyHinkle Apologies, it's prefixed with AND instead of WHERE - the last subquery. – unsigned Mar 1 at 19:19
  • @Dan What a cool tool, thanks! brentozar.com/pastetheplan/?id=HkD6BZDLN – unsigned Mar 1 at 19:19
  • I understand that MAX(SCSUB.CREATED) will return just one row. So, why don't you try moving that subquery on the top of the script, and then setting that result into a @variable? In that case, you will be able use something like SC2.CREATED = @variable. – Dan Mar 1 at 20:19
  • @Dan thanks for your help. Tried with the same result. Unfortunately the subquery itself runs for ~30 seconds, even when the outer reference (first WHERE condition) is commented out. I am seeking to optimize or remove joins from the subquery - the outer query performs as expected. – unsigned Mar 1 at 20:31
  • @Dan I had the same idea, but OP is joining on tables from the main query inside the subquery – Randi Vertongen Mar 1 at 20:38
1

It looks like you have very few indexes in there. If this isn't a third-party app then here are some indexes I'd test out. I'm making some assumptions, such as Row_ID is a unique column.

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
)

Also, I can't see this without saying something. You appear to have a social security number unencrypted, used as a foreign key, assuming it's unique, etc.. This is a risk on many levels. If what I'm seeing is correct and you're not convinced it's a bad idea you should read https://www.computerworld.com/article/2552992/not-so-unique.html and https://helifromfinland.blog/2014/04/18/is-social-security-number-a-good-primary-key/

I've spent too much time on this for now. Try out these indexes in your test environment and see how much of a difference it makes. If you're able to make these types of changes and didn't know you should then start looking for blog posts introducing people to indexing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.