WHEN Add optional parameters on join query become very slow so How to

I work on SQL server 2012 I face issue when add this statement it take 9 minutes to display 900 rows only

SELECT fmat.Value as PLID,c.CodeType,
COUNT(DISTINCT tr.PartID) [#partsHasCodes]
into #partsHasCodes
FROM Parts.TradeCodes tr WITH(NOLOCK)
INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID
INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007
inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID) AND (c.PLID is null OR fmat.Value=c.PLID)
GROUP BY fmat.Value,c.CodeType

without add this statement below

AND (c.PLID is null OR fmat.Value=c.PLID)

query take 3 minute to display 900 rows and after add

AND (c.PLID is null OR fmat.Value=c.PLID)

it take 9 minutes

Are there are any way to make query above to be optional without take too much time

PLID IS optional if it have value then get his value and code type

if PLID not have value then select data based on code type

so why it take too much time

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
problem still exist
What I have tried:
this is execution plan so what I do
https://www.brentozar.com/pastetheplan/?id=HJXDEp_bv
what I do as below :
create clustered index idx on #TempPlAndCodeType (CodeTypeId)
but still take too much time
Navaneeth Krishnan6y ago
https://stackoverflow.com/questions/59047447/optional-parameter-in-sql-query-very-slow-when-checking-for-null