inner join performance issue with or condition

Question

i have a sp which returns 3 millions records. right now its taking 16 minute . i am trying to optimize it. since there is a or condition in join its taking time. is there any way to optimize it?

SELECT
    id, from
FROM
    [#Temp1] AD              
    JOIN [#Temp2]  SS
        ON AD.Id = SS.Id
        OR (SS.Code = AD.Code AND AD.ID IS NULL)        
JOIN wTable1 WSS WITH(NOLOCK)               
    ON SS.PId= WSS.Id

That query, as it stands, won't even run. `from FROM`? Considering you're selecting from 2 temporary tables, however, then this suggests there are no indexes on either of these tables and hence why it's slow. Why not query directly against the original tables? — Thom A, Feb 08 '19 at 21:10
I suspect so, however, `FROM` is a reserved word in SQL Server, so it would need to be quoted for the above SQL to actually work. — Thom A, Feb 08 '19 at 21:23
Without knowing table structures, including indexes as well as approximate row counts for the persistent and temp tables this is guessing at best. — Sean Lange, Feb 08 '19 at 21:28
Is the `,` after `id` and before `from` on first line a mistake? And what take does that id refer to? It's not even qualified. Is that correct too? — JGFMK, Feb 08 '19 at 21:42
Also, please prepend the column names you are retrieving (`id` and `from`) with the table aliases. It's not clear to us where those columns are coming from. — The Impaler, Feb 08 '19 at 21:57
On the off chance that SS.ID would be NULL anytime ad.ID is NULL, you could do On ISNULL(Ad.ID, AD.CODE) = ISNULL(ss.id = ss.code) — Robert Sievers, Feb 08 '19 at 22:23
Can you please use SET STATISTICS IO ON; SET STATISTICS TIME ON; SET SHOWPLAN_XML ON; We would need to see the whole plan and the table reads associated with this hotspot to understand exactly what is going on. — John Zabroski, Feb 08 '19 at 22:53

score 1 · Answer 1 · answered Feb 08 '19 at 22:50

Often the best approach is to use left joins:

select id, 
       coalesce(wss1.col, wss2.col) as col
from [#Temp1] AD left join            
     [#Temp2] SS1
     ON AD.Id = SS1.Id left join
     [#Temp2] SS2
     ON SS2.Code = AD.Code AND AD.ID IS NULL LEFT JOIN
     wTable1 WSS1            
     ON WSS1.Id = SS1.PID LEFT JOIN
     wTable1 WSS2
     ON WSS2.ID = SS2.PID
WHERE SS1.ID IS NOT NULL OR SS2.CODE IS NOT NULL;  -- one of the joins works

I cannot promise that this works in your specific case, because you have not provided sample data and desired results. However, this approach does work in similar cases.

Martin Smith · Answer 2 · 2019-02-08T23:06:49.250

As stated in the likely duplicate Is having an 'OR' in an INNER JOIN condition a bad idea? this will force a plan with nested loops and often replacing it with UNION pays dividends (UNION ALL in this case as the branches are mutually exclusive). In the below each branch of the UNION ALL can use hash or merge join as they are straightforward equi joins)

It is not clear what table each column comes from in your query but the basic approach is

WITH SS
     AS (SELECT SS.Id,
                SS.PId
         FROM   [#Temp1] AD
                JOIN [#Temp2] SS
                  ON AD.Id = SS.Id
         WHERE  AD.ID IS NOT NULL
         UNION ALL
         SELECT SS.Id,
                SS.PId
         FROM   [#Temp1] AD
                JOIN [#Temp2] SS
                  ON AD.Code = SS.Code
         WHERE  AD.ID IS NULL)
SELECT SS.Id,
       WSS.[from]
FROM   SS
       JOIN wTable1 WSS 
         ON SS.PId = WSS.Id

inner join performance issue with or condition

2 Answers2