How to rewrite query below with best practise way ?

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;

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

1 Reply

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