Seeking Help Optimizing Self-Joins

Question

This query takes ~5x longer than it should due to the WHERE IN() statement at the end. Is there a way to check IN() the resultset of the outer query?

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

Answer 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.

Answer 2

IMO, your query is not Optimize at all.It is not fit for Index tuning.

May be some Index gives you temporary relief.

Also it is not possible to rewrite your query because lack of Table Design,Data and Requirement.

So my query is there to just give you idea,but I am 100% sure about my idea.

My suggestion,

1. Explicitly use Join in query for Readability purpose.

It is hard to catch which table is connected with which table and by which column.

1. Use Temp table to store repeated result-set.May be If I know all the require info then suggest you to use CTE.

This way optimizer has to process limited result-set again and again.

Like ,you can put the value of siebel.ODS_S_CASE_BNFTPLAN in #BNFTPLAN table along with this filter.

    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`

Don't forget to read my comment and remember that my query is only rough work, you have to implement it perfectly.

Put the value of siebel.ODS_S_CASE in #ODS_S_CASE table

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'

Notice how I have put where condition SC.STATUS_CD = 'Active' and also join #BNFTPLAN CBP and was trying to use MCON.ROW_ID

Carefully applying where condition and join while inserting in Temp table will limit the result set.So that lot of rows are not process again and again by optimizer.

This will give accurate Cardianility Estimate and help optimizer in making correct plan.

This will improve your query performance by ~3x or ~5x.

At this point you can show as your query for Index tuning or further improvement.