Can make query below take less time this when run on SQL server 2012 ?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008

, +

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 14-Jun-21 0:54am

ahmed_sa

Updated 14-Jun-21 3:56am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2021-06-14T03:56:00

Solution 1

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

Posted 14-Jun-21 3:56am

Wendelius

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

For question 1, see Create Foreign Key Relationships - SQL Server | Microsoft Docs[^]

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[^]