can i enhance this query to run fast on sql server 2012 ?

Question

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

Answer 1

If you have 17.62 million data, then thirty minutes may not be too slow. I have not run tens of millions of data, so I did a simple search and found that the running time of tens of millions of data is not too slow for 30 minutes.

My work computer hardware is pretty good, and it takes one to two minutes to run one million pieces of data.

From the perspective of the statement, the overhead of the insert statement is as high as 62%.This is obviously an excessive overhead.

  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)

Then the cost of this statement is too high.But today I have no time to study how to optimize this sentence, tomorrow I will provide you with suggestions.

Answer 2

I found two ways to solve this problem:
The efficiency of SELECT INTO is much higher than that of INSERT INTO SELECT. So you can replace the INSERT INTO SELECT statement with a SELECT INTO statement.
Please refer ro:

SELECT distinct r.familyid,r.GroupID,
IIF(r.PortionKey='blank','',r.PortionKey) PortionKey1
,IIF(m.PortionKey='blank','',m.PortionKey) PortionKey2
INTO GetFinalResult 
FROM   dbo.GetFinalResultParts r with(nolock)
inner join dbo.GetFinalResultMasks m with(nolock) on r.groupid=m.groupid 
and r.familyid=m.familyid and (r.[PortionKey] = m.[PortionKey]) 
WHERE len(r.portionkey)=len(m.portionkey)

The SELECT INTO syntax is that the table to be inserted cannot exist in advance, so I removed the table variable.

The second method is that I have seen someone find a solution for a similar problem. I don’t know if you have update or delete at the same time, but you can refer to:

When insert into xx select data volume reaches 100,000 level, there are Update and Delete operation tables at the same time. It is easy to cause a lock table.

After research, the SQL statement problem is solved. The specific solutions are as follows:

1. The update and Delete operations are treated as a transaction
2. Delete the index before Insert into
3. Divide the data that needs to be inserted into into no more than 3000 pieces/time for execution.

Answer 3

I would try changing your table variable @GetFinalResult to a temp table #GetFinalResult. Table variables, as a general rule, should not be used for >1000 rows.

Answer 4

;WITH 
cte
as(SELECT distinct r.familyid,r.GroupID,
IIF(r.PortionKey='blank','',r.PortionKey) PortionKey1,
IIF(m.PortionKey='blank','',m.PortionKey) PortionKey2,
ROW_NUMBER() OVER(ORDER BY familyid) rr
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))

insert into GetFinalResult 
SELECT familyid,GroupID,PortionKey1,PortionKey2
FROM  cte
WHERE rr<3000

;WITH 
cte
as(SELECT distinct r.familyid,r.GroupID,
IIF(r.PortionKey='blank','',r.PortionKey) PortionKey1,
IIF(m.PortionKey='blank','',m.PortionKey) PortionKey2,
ROW_NUMBER() OVER(ORDER BY familyid) rr
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))

insert into GetFinalResult 
SELECT familyid,GroupID,PortionKey1,PortionKey2
FROM  cte
WHERE rr between 3000 and 6000
...

I think this is a bit troublesome, but maybe you can try to split it into a larger range.