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.CodeTypeId,
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)
GROUP BY fmat.Value,c.CodeTypeId
without add this statement below
(c.CodeTypeId=tr.CodeTypeID)
query take 3 minute to display 900 rows and after add
(c.CodeTypeId=tr.CodeTypeID)
it take 9 minutes
when join with temp table #TempPlAndCodeType time become 9 minutes.
What I have tried:
this is execution plan so what I do
https:
what I do as below :
create clustered index idx on #TempPlAndCodeType (CodeTypeId)
but still take too much time