Click here to Skip to main content
14,543,577 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Updated 16-Mar-20 3:29am
v5
Rate this:
Please Sign up or sign in to vote.

Solution 3

Now that you have shared some information about your table structures I can point you at one thing that could be better … there is no need at all for a sub-query here
INNER JOIN 
(
	SELECT c.CompanyID, C.CompanyName 
	FROM Z2DataCompanyManagement.CompanyManagers.Company c 
)AS Companies ON Companies.CompanyID = Total.CompanyID 
Just use
INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c ON c.CompanyID = Total.CompanyID
You could probably get rid of all of the sub-queries to be honest, and get something like
SELECT 
c.CompanyName [CompanyName],
COUNT(DISTINCT PartsFamilyID) [Total Family Count],
COUNT(PartID) [Total Part Count],
COUNT(DISTINCT p.PartsFamilyID) [Done Family Count],
COUNT(p.PartID) [Done Part Count]
FROM Parts.Nop_Part p
LEFT JOIN Parts.FamilyIntroductionDate f ON p.PartsFamilyID = f.FamilyID
INNER JOIN Z2DataCompanyManagement.CompanyManagers.Company c ON c.CompanyID = p.CompanyID 
GROUP  BY c.CompanyName
ORDER BY c.CompanyName
but as I can't run the code to create your tables and you haven't provided any sample data for me to check the results, I'm not going to dig much deeper - so be aware the above code is untested
   
Comments
Maciej Los 16-Mar-20 10:06am
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 1

Without knowing nothing about your sql server, database structure, etc. we can't help you.

I'd suggest to read these:
SQL Server Performance Tuning Tips[^]
Query optimization techniques in SQL Server: tips and tricks[^]
   
Comments
Patrice T 16-Mar-20 4:39am
   
Be nice with Ahmed, he is only 250+ questions (a newbee) and still didn't learned how to give useful informations when asking for help. :)
Maciej Los 16-Mar-20 6:29am
   
Thanks! I've been warned...
;)
ahmed_sa 16-Mar-20 6:29am
   
can you see execution plan above
Maciej Los 16-Mar-20 6:33am
   
Yeah... I can see, but in what aspect this information is useful to me?
As i mentioned in my answer: there's a lot of things that may influence on sql server/query performance.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100