ahmed elbarbary

ahmed elbarbary

  • 642
  • 1.6k
  • 282.2k

How to rewrite query below with best practise way ?

Mar 16 2020 1:50 AM
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:
  1. SELECT   
  2. Companies.CompanyName [CompanyName],  
  3. ISNULL(Total.FamilyTotal,0) [Total Family Count],  
  4. ISNULL(Total.Partstotal, 0) [Total Part Count],  
  5. ISNULL(Done.DoneFamily, 0) [Done Family Count],  
  6. ISNULL(Done.DoneParts, 0) [Done Part Count],  
  7. ISNULL(NotDone.NotDoneFamily, 0) [NotDone Family Count],  
  8. ISNULL(NotDone.NotDoneParts, 0) [NotDone Part Count]  
  9. FROM  
  10. (  
  11. SELECT  CompanyID, COUNT(DISTINCT PartsFamilyID) FamilyTotal, COUNT(PartID) Partstotal  
  12. FROM Parts.Nop_Part   
  13. GROUP  BY CompanyID  
  14. )AS Total  
  15. LEFT JOIN   
  16. (  
  17. SELECT   
  18. p.CompanyID,  
  19. COUNT(DISTINCT p.PartsFamilyID) DoneFamily,  
  20. COUNT(p.PartID) as DoneParts  
  21. from Parts.FamilyIntroductionDate f  
  22. inner JOIN Parts.Nop_Part p  
  23. ON p.PartsFamilyID = f.FamilyID  
  24. GROUP  BY p.CompanyID  
  25. )AS Done ON Done.CompanyID = Total.CompanyID  
  26. LEFT JOIN   
  27. (  
  28. SELECT   
  29. p.CompanyID,  
  30. COUNT(DISTINCT p.PartsFamilyID) NotDoneFamily,  
  31. COUNT(p.PartID) as NotDoneParts  
  32. from Parts.Nop_Part p   
  33. LEFT JOIN Parts.FamilyIntroductionDate f  
  34. ON p.PartsFamilyID = f.FamilyID  
  35. WHERE f.FamilyID IS NULL  
  36. GROUP  BY p.CompanyID  
  37. )AS NotDone ON NotDone.CompanyID = Total.CompanyID  
  38. INNER JOIN   
  39. (  
  40. SELECT c.CompanyID, C.CompanyName   
  41. FROM Z2DataCompanyManagement.CompanyManagers.Company c   
  42. )AS Companies ON Companies.CompanyID = Total.CompanyID   
  43. ORDER BY Companies.CompanyName  
  44.   
  45. END
 

Answers (1)