CODEWITHSUNDEEP
sqlsql-server
Patrick Brielmayer
Patrick Brielmayer

Reputation: 272

Performance issue on conditional join in select query

Right now, I am selecting data with this query (simplyfied):

SELECT person.*, pty.*
FROM PERSON person
     LEFT JOIN PARTNER partner 
     ON partner.FK_BPE_ISTC_ID = person.BSPE_ISTC_ID
     INNER JOIN PTY pty 
     ON pty.PTY_ISTC_ID = IIF(person.FORGN_ISTC_IND != 0, person.FORGN_ISTC_IND, partner.FRGN_ISTC_ID)

The Problem is that it takes so much time to complete and the execution plan looks like this:

Execution plan

Almost 20 million rows (picture) need to be processed to get me 25 thousand rows in the end.

Is there a better approach to join the PTY table with the condition?

Upvotes: 0

Views: 529

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

IIF function used in the ON condition will make the query non sargable

Below query will perform better when there is a index on any of these columns person.FORGN_ISTC_IND, person.FORGN_ISTC_IND, partner.FRGN_ISTC_ID

SELECT person.*,
       pty.*
FROM   PERSON person
       LEFT JOIN PARTNER partner
              ON partner.FK_BPE_ISTC_ID = person.BSPE_ISTC_ID
       INNER JOIN PTY pty
               ON ( person.FORGN_ISTC_IND != 0
                    AND pty.PTY_ISTC_ID = person.FORGN_ISTC_IND )
                   OR pty.PTY_ISTC_ID = partner.FRGN_ISTC_ID

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Do multiple left joins:

SELECT person.*, 
       COALESCE(ptyp.col1, ptypa.col1) as col1,
       . . .
FROM PERSON p LEFT JOIN PARTNER
     partner pa
     ON pa.FK_BPE_ISTC_ID = p.BSPE_ISTC_ID LEFT JOIN
     pty ptyp
     ON p.FORGN_ISTC_IND <> 0 AND ptyp.PTY_ISTC_ID = p.FORGN_ISTC_IND LEFT JOIN
     pty ptypa
     ON pty.PTY_ISTC_ID = 0 AND ptypa.PTY_ISTC_ID = pa.FRGN_ISTC_ID;

SQL Server can do a much better job optimizing simple join conditions. You need to choose the appropriate value in the select.

Upvotes: 3

Related Questions