Hi I have below query in an SP
@CrmContactId is a parameter to the SP.
Select distinct A.PolicyBusinessId, A.PolicyDetailId
from TPolicyBusiness A
inner join TPolicyOwner B on a.PolicyDetailId=b.PolicyDetailId
Left Join TAdditionalOwner C on c.PolicyBusinessId=A.PolicyBusinessId
where (b.CRMContactId = @CRMContactId)
we made a new change and introduced an OR condition
Select distinct A.PolicyBusinessId, A.PolicyDetailId
from TPolicyBusiness A
inner join TPolicyOwner B on a.PolicyDetailId=b.PolicyDetailId
Left Join TAdditionalOwner C on c.PolicyBusinessId=A.PolicyBusinessId
where (b.CRMContactId = @CRMContactId OR C.CRMContactId = @CRMContactId)
Execution plan:
Attachment : Execution Plan
but this change caused a huge performance issue in live server. TPolicyBusiness and TPolicyOwner are heavy tables having millions of records. TAdditionalOwner table is a light table with only few records.
To fix this issue Union all was instead of OR condition.
Select distinct A.PolicyBusinessId, A.PolicyDetailId
From
(
Select A.PolicyBusinessId, A.PolicyDetailId
from TPolicyBusiness A
inner join TPolicyOwner B on a.PolicyDetailId=b.PolicyDetailId
where b.CRMContactId = @CRMContactId
union all
Select A.PolicyBusinessId, A.PolicyDetailId
from TPolicyBusiness A
Join TAdditionalOwner C on c.PolicyBusinessId=A.PolicyBusinessId
where C.CRMContactId = @CRMContactId
) as A
Execution Plan:
Attachement Execution Plan
Could someone please explain why the introduction of OR caused an issue and why using Union is better than OR in this case?