Click here to Skip to main content
14,977,676 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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

SQL
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)


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

What I have tried:

SQL
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
Posted
Updated 14-Jun-21 3:56am
Comments
Richard MacCutchan 14-Jun-21 7:08am
   
Why are you using float values for identity fields, when integers are available?
ahmed_sa 14-Jun-21 7:16am
   
you are correct it must be int
so are there are any thing other can enhancement

1 solution

Few things to consider

  • based on the script the original tables have no indexes, why? You should index the columns you use to join tables
  • you have no foreign keys defined. I take it the id columns still reference other tables. First of all, foreign keys help you to prevent incorrect data but they are also a good hint for the optimizer.
  • why length of the portionkey is relevant instead of the content? This sounds peculiar but if it is really what you need, why not make it a computed, persisted column and index it
  • why do you use intermediate table (@GetFinalResult) in your query, why not simply define it in the common table expression and reference the table there without inserting it to a table variable
   
Comments
ahmed_sa 14-Jun-21 10:10am
   
1- can you tell me how foreign key will be
2- what i don on length of portionkey
3- how to use cte instead of @GetFinalResult
Wendelius 14-Jun-21 10:24am
   
Wendelius 14-Jun-21 10:24am
   
I don't quite understand the second question
Wendelius 14-Jun-21 10:26am
   
For question 3, something like

;WITH
gfr as (
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)
),
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 gfr t1
inner join gfr 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 gfr 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
ahmed_sa 14-Jun-21 10:57am
   
according to point 1,3 and 4 is ok
but point 2 portion key length how to handle it best practice
Wendelius 14-Jun-21 11:01am
   
if you need to match the length, try using a computed, persisted, and indexed column, see Specify Computed Columns in a Table - SQL Server | Microsoft Docs[^]

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900