CODEWITHSUNDEEP
sql-servert-sql
DinaDee
DinaDee

Reputation: 287

OPTIMIZING LEFT JOIN in SQL SERVER

SELECT h.*
FROM  h
 LEFT JOIN  au ON au.ID = h.Key AND au.RaID = 40190 AND h.EntityType = 'Detail'
 LEFT JOIN  ip ON ip.ID = h.Key AND ip.RaID = 40190 AND h.EntityType = 'itempart'
WHERE 
coalesce(au.id,ip.id) is not null

Anyone have a good idea how to optimize this? h is HUGE history log table.

Upvotes: 1

Views: 1952

Answers (1)

David
David

Reputation: 3773

what about using a union on your allowed types then you can use an inner join:

SELECT h.*
FROM  h
    INNER JOIN (
                SELECT ID, 'Detail' AS EntityType FROM au WHERE (RaID = 40190)
                UNION ALL
                SELECT ID, 'itempart' AS EntityType FROM ip WHERE (RaID = 40190)
            ) AS filt
            ON h.Key = filt.ID
            AND h.EntityType = filt.EntityType

also make sure you have indexes on your filtered/joined columns. And as always when looking for performance issues check the query plan as n8wrl suggests above.

Upvotes: 2

Related Questions