June 14, 2021 at 10:12 am
I run query below it take too much time it reach to 30 minue
so I need enhance it to get 5 minute or 10 minute at maximum if less it is good
this is my execution plan as below :
https://www.brentozar.com/pastetheplan/?id=ryIvvs4od
this is my script as below :
script for small sample from tables used and input
CREATE TABLE dbo.GetFinalResultParts(
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
CREATE TABLE dbo.GetFinalResultMasks(
[PortionKey] [nvarchar](255) NULL,
[GroupID] [float] NULL,
[familyid] [float] NULL
) ON [PRIMARY]
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)
DECLARE @GetFinalResult as table
( [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
[PartNumber] [varchar](200) NOT NULL INDEX IXkpart NONCLUSTERED,
[MaskNumber] [varchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)
insert into @GetFinalResult
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey) ,IIF(m.PortionKey='blank','',m.PortionKey)
FROM extractreports.dbo.GetFinalResultParts r with(nolock)
inner join extractreports.dbo.GetFinalResultMasks m with(nolock) on r.groupid=m.groupid and r.familyid=m.familyid and (r.portionid = m.portionid or m.portionid= 0)
where len(r.portionkey)=len(m.portionkey)
;WITH cte AS (
SELECT t1.familyid,t2.GroupID,cast((t1.PartNumber+ t2.PartNumber) as varchar(200)) PartNumber,cast((t1.MaskNumber+t2.MaskNumber) as varchar(200)) MaskNumber
FROM @GetFinalResult t1
inner join @GetFinalResult t2 on t1.groupid=1 and t2.groupid=2
WHERE t1.GroupID = 1
UNION ALL
SELECT t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as varchar(200)) PartNumber,cast((s.MaskNumber+t.MaskNumber) as varchar(200)) MaskNumber
FROM @GetFinalResult t INNER JOIN
cte s ON t.GroupID = s.GroupID + 1
)
SELECT familyid,PartNumber,MaskNumber
into extractreports.dbo.getfinaldatapc
from cte
where GroupID =(select max(GroupID) from extractreports.dbo.GetFinalResultMasks with(nolock))
group by familyid,PartNumber,MaskNumber
result returned from query as below
(126 row(s) affected)
(1 row(s) affected)
(17625600 row(s) affected)
(1 row(s) affected)
so can you help me enhance it to take less time
June 14, 2021 at 1:35 pm
You have no primary keys or indexes. SQL Server has to scan the table for every query/subquery/CTE.
Your datatypes seem questionable.
Naming tables starting with Get is somewhat unconventional -- the verb is generally used for procedures or functions.
June 14, 2021 at 1:48 pm
ok you are correct
i will change data type
are there are any other thing can change it to enhance performance
June 14, 2021 at 1:53 pm
when do like that
cast((t1.PartNumber+ t2.PartNumber) as varchar(200)) PartNumber
can i concave two string without make cast when insert data
June 14, 2021 at 7:14 pm
If you are asking if the CAST can be part of an insert, it can.
June 14, 2021 at 7:20 pm
where len(r.portionkey)=len(m.portionkey)
The functions are going to prevent SQL Server from using indexes effectively even if you had them. It also seems to indicate that portion key is really a composite of two or more attributes.
June 15, 2021 at 2:02 am
Your code has an rCTE(Recursive CTE) in it that is reading 1 row at a time for a total of 21.45 million rows probably due to the result of an accidental Cross-Join in the form of a many-to-many relationship created by having many rows in each GroupID in a table variable that is not capable of creating statistics to go with it and that's aggregating down to a "mere" 17.6 million rows thanks in large part to a distinct sort from a GROUP BY and stuffing the 17 million rows into a table.
I'm thinking there's something seriously wrong with the requirements of whatever it is that you're supposed to produce and that needs to be revisited to figure out what really needs to be done and why. For example, why do you need a recursive query to step through what looks like a hierarchy of parts.
To answer your question, the best thing to do would be to forget everything you think you know about this and start over. Write code that resolves one problem at a time and, for the love of Pete, stop using Table Variables for this many rows and make bloody sure that you preaggregate your Groups before joining multirow groups to each other.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply