why this query is very slow when add these 4 lines ?

the query is very slow i test it take too much time i realized that problem on these lines

COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS NULL THEN p.PartID END ) Count_No_InTroudctionNULLForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL THEN p.PartID END ) Count_No_InTroudctionNotNULLForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL AND isnull(PF.Comment,'') LIKE '%appro%' THEN PF.PartID END ) Count_No_InTroudctionNotNULLAPPForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL AND isnull(PF.Comment,'') NOT LIKE '%appro%' THEN PF.PartID END ) Count_No_InTroudctionNotNULLNoAPPForeCast

because all query take one 50 minutes

when i remove lines above query take 7 minutes

so how to solve this issue as block blow

SELECT F.CompanyID ,
COUNT(CASE WHEN FI.FamilyLevel= 1 THEN p.PartID END ) Count_Yes_InForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 THEN p.PartID END ) Count_No_InForeCast ,
--------------- block have problem
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS NULL THEN p.PartID END ) Count_No_InTroudctionNULLForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL THEN p.PartID END ) Count_No_InTroudctionNotNULLForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL AND isnull(PF.Comment,'') LIKE '%appro%' THEN PF.PartID END ) Count_No_InTroudctionNotNULLAPPForeCast ,
COUNT(CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 AND PF.IntroductionDate IS not NULL AND isnull(PF.Comment,'') NOT LIKE '%appro%' THEN PF.PartID END ) Count_No_InTroudctionNotNULLNoAPPForeCast ,
--------------- end
COUNT(DISTINCT CASE WHEN FI.FamilyLevel= 1 THEN FI.FamilyID END ) CountFamily_Yes_InForeCast ,
COUNT(DISTINCT CASE WHEN ISNULL(FI.FamilyLevel,0)= 0 THEN FI.FamilyID END ) CountFamily_No_InForeCast into #ForecastTotal
FROM Parts.FamilyIntroductionDate FI WITH(NoLock) INNER JOIN parts.Nop_PartsFamily F ON F.PartFamilyID =fi.FamilyID
INNER JOIN parts.Nop_Part P WITH(NoLock) ON p.PartsFamilyID=f.PartFamilyID
LEFT OUTER JOIN parts.PartsForecast PF WITH(NoLock) ON pf.PartID=p.PartID
GROUP BY F.CompanyID

execution plan as below

https://www.brentozar.com/pastetheplan/?id=Hyss6rlLL

4 Replies

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

ahmed elbarbary6y ago
I have already non cluster index on IntroductionDate
Sreekanth Reddy6y ago
@ahmed elbarbary

You are using the aggregation function(count) over a field with case when, So we cant do any more over there. I feel applying the non-clustered may vary the result.

Mark as answer, If it helps.
Thank you.
ahmed elbarbary6y ago
thank you for reply
can you help me with any way to write these 4 lines with any way best practise
to avoid long time
Sreekanth Reddy6y ago
@ahmed elbarbary

Please add Non-Clustered Index to the column "IntroductionDate" and check once. Hope it reduces the cost of execution time.

Mark as answer, If it helps. Thank you.