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)
so can you help me enhance it to take less time