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

  • 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

  • You have no primary keys or indexes. SQL Server has to scan the table for every query/subquery/CTE.

    Your datatypes seem questionable.

    • Is PortionKey really ever unicode? Your example shows only ASCII strings.
    • Is GroupID really ever float? Your example shows only tinyints.
    • Is familyid really ever float? Your example shows only integers.

     

    Naming tables starting with Get is somewhat unconventional -- the verb is generally used for procedures or functions.

     

  • ok you are correct

    i will change data type

    are there are any other thing can change it to enhance performance

  • when do like that

    cast((t1.PartNumber+ t2.PartNumber) as varchar(200)) PartNumber

    can i concave two string without make cast  when insert data

  • If you are asking if the CAST can be part of an insert, it can.

  • where len(r.portionkey)=len(m.portionkey)  

    The functions are going to prevent SQL Server from using indexes effectively even if you had them. It also seems to indicate that portion key is really a composite of two or more attributes.

  • Your code has an rCTE(Recursive CTE) in it that is reading 1 row at a time for a total of 21.45 million rows probably due to the result of an accidental Cross-Join in the form of a many-to-many relationship created by having many rows in each GroupID in a table variable that is not capable of creating statistics to go with it and that's aggregating down to a "mere" 17.6 million rows thanks in large part to a distinct sort from a GROUP BY and stuffing the 17 million rows into a table.

    I'm thinking there's something seriously wrong with the requirements of whatever it is that you're supposed to produce and that needs to be revisited to figure out what really needs to be done and why.  For example, why do you need a recursive query to step through what looks like a hierarchy of parts.

    To answer your question, the best thing to do would be to forget everything you think you know about this and start over.  Write code that resolves one problem at a time and, for the love of Pete, stop using Table Variables for this many rows and make bloody sure that you preaggregate your Groups before joining multirow groups to each other.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply