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

ahmedbarbary · June 14, 2021, 10:14am

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 :

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

harishgg1 · June 14, 2021, 2:35pm

Things to consider

-- distinct
-- IIF
-- len
-- UNION ALL
-- DATA count .. in each part
-- Small Temp Hash Tables joined bit by bit for Final Result
-- Indexes

robert_volk · June 14, 2021, 2:37pm

A few housekeeping things:

Thanks for the DDL, but it's incomplete as it's missing columns from the Masks table (portionID). I can't run the SQL you posted without errors.
Thanks for sample data, but sample results also help a lot, so we can verify that we're getting the results you expect. It also helps you to validate that you're getting the right results. At first glance, I'd question why you'd want 17 million+ rows in a result. Obviously we don't want/need millions of sample results, but only enough to clearly define an example we can validate to the source data.
Suggest changing the table variable to a temp table, as table variable statistics (even with indexes) don't always get picked up by the query optimizer. It may not matter for this particular query, but as a general practice it's probably a better idea.
And it would really help if you explain WHAT you're trying to achieve. I see that you're concatenating multiple part numbers across multiple rows to construct a new part number, based on a relationship to a mask of some kind, and aggregated/grouped by GroupID and FamilyID. This is where sample data and output is critical to illustrate what you're trying to get.

The first thing I would check is your WHERE clause:

  where GroupID =(select max(GroupID) from extractreports.dbo.GetFinalResultMasks with(nolock)) 
  group by familyid,PartNumber,MaskNumber

This is typically a bad pattern, because you're not correlating the subquery (in parentheses) to the outer query. You're getting a CROSS JOIN with every row, basically multiplying the row counts of each table. I think you probably want this:

WHERE cte.GroupID=(SELECT max(M.GroupID) 
FROM extractreports.dbo.GetFinalResultMasks M
WHERE cte.FamilyID=M.FamilyID) -- possibly other conditions for Part and Mask?
GROUP BY cte.familyid, cte.PartNumber, cte.MaskNumber

Whether this will improve performance I cannot say. Based on the query plan you posted, that subquery is causing the large Table Spool (21 million+ rows). I can't do any additional analysis as the plan XML on the site has an error and I can't load it in to Plan Explorer. In any case, the table variable @GetFinalResult is only accessed by a table scan, so the indexes you created aren't helping.

You also have GroupID and FamilyID declared as float in some tables, and int in others. This is a data type mismatch that will cause implicit conversions, some of which may not work. Float is an approximate data type, int is a precise type, and loss of precision may affect the results you get. Float is also a bad choice for key/JOIN columns or for any operation based on equality comparisons. This is why you see the Compute Scalar operators in the query plan.

You also have operations of 17 million and 21 million rows going into nested loops, which will never perform well. These are then being put into a DISTINCT SORT that is then INSERTed into another table. Again, I think you should verify if 17 million rows is your actual desired output. If it is, then you're going to see long-running queries simply due to the volume you're generating.

I also recommend using SentryOne Plan Explorer, it has better analysis tools and is a free download:

ahmedbarbary · June 14, 2021, 3:04pm

can you help me on that
cast((t1.PartNumber+ t2.PartNumber) as varchar(200))
i don't need to make cast
so are there are any thing make me avoid
cast

yosiasz · June 14, 2021, 3:11pm

Please fix the script first and all the things @robert_volk asked of you? Before posting it is always good to test it on your own non prod server. I have created a sqlteam database where I try everything on.

ahmedbarbary · June 15, 2021, 10:29am

correct script as below :slight_smile: 

CREATE TABLE dbo.GetFinalResultParts(
       [PortionKey] [nvarchar](255) NULL,
       [GroupID] [int] NULL,
       [familyid] [int] NULL,
	   portionid int
   ) ON [PRIMARY]
            
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'T496', 1, 7524090,10)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'X', 2, 7524090,20)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'B', 2, 7524090,30)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'754', 3, 7524090,40)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'755', 3, 7524090,50)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'K', 4, 7524090,60)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'M', 4, 7524090,70)
   INSERT dbo.GetFinalResultParts ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'blank', 4, 7524090,80)
            
            
   CREATE TABLE dbo.GetFinalResultMasks(
       [PortionKey] [nvarchar](255) NULL,
       [GroupID] [int] NULL,
       [familyid] [int] NULL,
	   portionid int
   ) ON [PRIMARY]
            
            
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'T496', 1, 7524090,10)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'X', 2, 7524090,20)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'B', 2, 7524090,30)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'754', 3, 7524090,40)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'755', 3, 7524090,50)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES (N'_', 4, 7524090,60)
   INSERT dbo.GetFinalResultMasks ([PortionKey], [GroupID], [familyid],portionid) VALUES ('blank', 4, 7524090,70)

  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    dbo.GetFinalResultParts r with(nolock)
  inner join 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 dbo.GetFinalResultMasks  with(nolock)) 
  group by familyid,PartNumber,MaskNumber


  
  select * from extractreports.dbo.getfinaldatapc

so I have two issue i need to make it to enhance performance
first thing how to merge string without using cast as

,cast((s.PartNumber+ t.PartNumber) as varchar(200)) PartNumber,cast((s.MaskNumber+t.MaskNumber) as varchar(200)) MaskNumber

second point how to avoid insert
on execution plan above insert represent 62 % percent and this is more
so how to solve issue of insert by any thing enhance performance

robert_volk · June 15, 2021, 2:15pm

CASTing isn't the issue, it's the PortionKey being declared as nvarchar and the PartNumber declared as varchar. You need to pick one or the other. Unless you absolutely know you need to support unicode characters in part numbers, I'd suggest using varchar only.

Is there an upper limit to the number of GroupIDs that are used to construct part numbers? The example data only goes up to 4, can it go higher than that, and if so, what's the absolute highest?