Как переписать запрос ниже с помощью наилучшей практики ?

---

Я работаю на SQL server 2012 мне нужно переписать запрос с гораздо большим количеством лучших практик и хорошего
для выполнения
потому что запрос ниже займет у двоих много времени

структура базы данных

CREATE TABLE [Parts].[Nop_Part](
	[PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[PartNumber] [nvarchar](70) NOT NULL,
	[CompanyID] [int] NOT NULL,
	[PartsFamilyID] [int] NOT NULL,
 CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED 
(
	[PartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_Partnon_LatestCompanyID] UNIQUE NONCLUSTERED 
(
	[LatestCompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [uc_partnumbernonCompany] UNIQUE NONCLUSTERED 
(
	[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
=========================================
CREATE TABLE [Parts].[FamilyIntroductionDate](
	[FamilyID] [int] NOT NULL,
	[IntroductionDate] [int] NULL,
	[FamilyLevel] [bit] NULL,
 CONSTRAINT [PK__FamilyIn__41D82F4BF2ECF001] PRIMARY KEY CLUSTERED 
(
	[FamilyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
=========================================
CREATE TABLE [CompanyManagers].[Company](
	[CompanyID] [int] IDENTITY(1,1) NOT NULL,
	[CompanyName] [nvarchar](250) NOT NULL,
	
 CONSTRAINT [PK__Company__2D971C4C74A2E1EE] PRIMARY KEY CLUSTERED 
(
	[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ__Company__9BCE05DC0B2C281C] UNIQUE NONCLUSTERED 
(
	[CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Экран плана выполнения
Общий доступ к файлам и их хранение упрощены[^]

Что я уже пробовал:

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]
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
INNER JOIN 
(
SELECT c.CompanyID, C.CompanyName 
FROM Z2DataCompanyManagement.CompanyManagers.Company c 
)AS Companies ON Companies.CompanyID = Total.CompanyID 
ORDER BY Companies.CompanyName

---