2
Answers

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

ahmed elbarbary

ahmed elbarbary
4y
587
1
I work on SQL server 2012 I face issue when add this statement it take 9 minutes to display 900 rows only
 
  1. SELECT fmat.Value as PLID,c.CodeType,    
  2. COUNT(DISTINCT tr.PartID) [#partsHasCodes]    
  3. into #partsHasCodes    
  4. FROM Parts.TradeCodes tr WITH(NOLOCK)     
  5. INNER JOIN Parts.Nop_Part pt WITH(NOLOCK) ON pt.PartID = tr.PartID    
  6. INNER JOIN Parts.Nop_PartsFamilyAttribute fmat WITH(NOLOCK) ON  fmat.PartFamilyID=pt.PartsFamilyID AND fmat.[Key]=20281007    
  7. inner join #TempPlAndCodeType c on (c.CodeTypeId=tr.CodeTypeID) AND (c.PLID is null OR fmat.Value=c.PLID)    
  8. 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
Answers (2)
Next Recommended Forum