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
Database Structure
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]
Execution plan Screen
File sharing and storage made simple[
^]
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]
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