Seeking Help Optimizing Self-Joins

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_SE

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.

StackExchange Database Administrators Q#231080, answer score: 2