How to rewrite query below with best practise way ?

Question

I work on SQL server 2012 I need to rewrite query with way more best practice and good
for performance
because query below take two much time

What I have tried:

SELECT
Companies.CompanyName [CompanyName],
ISNULL(Total.FamilyTotal,0) [Total Family Count],
ISNULL(Total.Partstotal, 0) [Total Part Count],
ISNULL(Done.DoneFamily, 0) [Done Family Count],
ISNULL(Done.DoneParts, 0) [Done Part Count],
ISNULL(NotDone.NotDoneFamily, 0) [NotDone Family Count],
ISNULL(NotDone.NotDoneParts, 0) [NotDone Part Count]
FROM
(
SELECT CompanyID, COUNT(DISTINCT PartsFamilyID) FamilyTotal, COUNT(PartID) Partstotal
FROM Parts.Nop_Part
GROUP BY CompanyID
)AS Total
LEFT JOIN
(
SELECT
p.CompanyID,
COUNT(DISTINCT p.PartsFamilyID) DoneFamily,
COUNT(p.PartID) as DoneParts
from Parts.FamilyIntroductionDate f
inner JOIN Parts.Nop_Part p
ON p.PartsFamilyID = f.FamilyID
GROUP BY p.CompanyID
)AS Done ON Done.CompanyID = Total.CompanyID
LEFT JOIN
(
SELECT
p.CompanyID,
COUNT(DISTINCT p.PartsFamilyID) NotDoneFamily,
COUNT(p.PartID) as NotDoneParts
from Parts.Nop_Part p
LEFT JOIN Parts.FamilyIntroductionDate f
ON p.PartsFamilyID = f.FamilyID
WHERE f.FamilyID IS NULL
GROUP BY p.CompanyID
)AS NotDone ON NotDone.CompanyID = Total.CompanyID
INNER JOIN
(
SELECT c.CompanyID, C.CompanyName
FROM Z2DataCompanyManagement.CompanyManagers.Company c
)AS Companies ON Companies.CompanyID = Total.CompanyID
ORDER BY Companies.CompanyName
END;

Vishvjit Shinde · Answer

Hi, Try this once SELECT CompanyID, COUNT(DISTINCT PartsFamilyID) FamilyTotal, COUNT(PartID) Partstotal into #Totals FROM Parts.Nop_Part GROUP BY CompanyID SELECT p.CompanyID, COUNT ( DISTINCT p.PartsFamilyID) DoneFamily, COUNT (p.PartID) as DoneParts into #totals from Parts.FamilyIntroductionDate f inner JOIN Parts.Nop_Part p ON p.PartsFamilyID = f.FamilyID GROUP BY p.CompanyID SELECT p.CompanyID, COUNT ( DISTINCT p.PartsFamilyID) NotDoneFamily, COUNT (p.PartID) as NotDoneParts into #NotDones from Parts.Nop_Part p LEFT JOIN Parts.FamilyIntroductionDate f ON p.PartsFamilyID = f.FamilyID WHERE f.FamilyID IS NULL GROUP BY p.CompanyID SELECT c.CompanyID, C.CompanyName into #companies FROM Z2DataCompanyManagement.CompanyManagers.Company c SELECT Companies.CompanyName [CompanyName], ISNULL (Total.FamilyTotal,0) [Total Family Count ], ISNULL (Total.Partstotal, 0) [Total Part Count ], ISNULL (Done.DoneFamily, 0) [Done Family Count ], ISNULL (Done.DoneParts, 0) [Done Part Count ], ISNULL (NotDone.NotDoneFamily, 0) [NotDone Family Count ], ISNULL (NotDone.NotDoneParts, 0) [NotDone Part Count ] FROM #Totals Total LEFT JOIN #totals Done ON Done.CompanyID = Total.CompanyID LEFT JOIN #NotDones NotDone ON NotDone.CompanyID = Total.CompanyID INNER JOIN #companies Companies ON Companies.CompanyID = Total.CompanyID ORDER BY Companies.CompanyName