0

Quick help to optimized query. I have below query which is running very slow

Select
P.*
from
dbo.Parent P
where
exists
(
Select 1
From
dbo.Child C
Where
1 = 1
and C.ColumnName1 = case when @p_ParameterValue is null then C.ColumnName1 else   P.ColumnName1 end
and C.ColumnName2 = isnull(@p_ParameterValue,C.ColumnName2)
)

It happens when we have value for parameter. When i try to normalize it. It works really. I have around 60K records in Parent and for each Column1 i have records in Child. ColumnName1 and ColumnName2 are clustered index on Child.

Select
P.*
from
dbo.Parent P
where
exists
(
Select 1
From
dbo.Child C
Where
1 = 1
and C.ColumnName1 = P.ColumnName1
and C.ColumnName2 = @p_ParameterValue
)
underscore
  • 6,495
  • 6
  • 39
  • 78
Asif Ghanchi
  • 1
  • 3

1 Answers1

0

You need to rewrite the conditions in WHERE to be sargable

Select P.*
from dbo.Parent P
where exists
(
  Select 1
  From dbo.Child C
  Where 1 = 1
  and (@p_ParameterValue is null OR C.ColumnName1 = P.ColumnName1)
  and (@p_ParameterValue is null OR C.ColumnName2 = @p_ParameterValue)
)
Community
  • 1
  • 1
TomT
  • 971
  • 7
  • 13