when using stuff query is very slow and take too much time so how to fix ?

ahmed salah 3,216 Reputation points
2021-11-29T23:57:27.393+00:00

I work on sql server 2014

the statement below without add two stuff feature name and feature value

it take 28 second

after add two stuff as below

it take 15 minute

i really need to know reason and how to fix it

SET @Sql= CONCAT('INSERT INTO ExtractReports.dbo.TAllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
                stuff(( SELECT  ''$'' + CAST( CP.ColumnName AS VARCHAR(500)) AS [text()]
                    FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from ExtractReports.dbo.TCondition C with(nolock) 
                    inner join core_datadefinitiondetails d with(nolock) on C.ZfeatureKey=d.columnnumber
                    INNER JOIN ExtractReports.dbo.TPartAttributes P with(nolock) on P.partid=PM.partid)CP
                    where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
                    ORDER BY CP.ZfeatureKey

                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  1, '''') as FeatureName,
                        stuff(( SELECT  ''$'' + CAST( CP2.Name AS VARCHAR(500)) AS [text()]
                    FROM(SELECT distinct P.partId,P.Name,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM ExtractReports.dbo.TCondition C2 with(nolock)
                    INNER JOIN ExtractReports.dbo.TPartAttributes P with(nolock) on C2.ZfeatureKey=P.ZfeatureKey)CP2
                    where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code and CP2.PartId=PM.partid
                    ORDER BY CP2.ZfeatureKey
                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  1, '''') as FeatureValue
                FROM 
                ExtractReports.dbo.TPartAttributes PM with(nolock) 
                INNER JOIN  ExtractReports.dbo.TCondition Co with(nolock) ON Co.ZfeatureKey = PM.ZfeatureKey ',             
                'Where (1=1 and  ',@Con ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                ' Having Count(1)>= ',(SELECT COUNT(1) FROM ExtractReports.dbo.TCondition with(nolock)))



            EXEC (@SQL)

execution plan as below

https://www.brentozar.com/pastetheplan/?id=HkXSLJQKt

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,300 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,520 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-30T03:38:06.357+00:00

    Hi @ahmed salah ,

    Maybe you can use CROSS APPLY.A simple example:

    CREATE TABLE #Temp(  
    ID varchar(50),  
    seat varchar(50),   
    code varchar(150))  
      
    INSERT INTO #Temp VALUES ('15098', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15098', '2', 'AA21');  
    INSERT INTO #Temp VALUES ('15105', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15105', '1', 'DD15');  
    INSERT INTO #Temp VALUES ('15105', '1', 'NN60');  
    INSERT INTO #Temp VALUES ('15196', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15196', '2', 'DD50');  
    INSERT INTO #Temp VALUES ('15196', '2', 'DD51');  
    INSERT INTO #Temp VALUES ('15209', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15209', '3', 'AA21');  
    INSERT INTO #Temp VALUES ('15209', '2', 'CC50');  
    INSERT INTO #Temp VALUES ('15209', '1', 'DD01');  
    INSERT INTO #Temp VALUES ('15209', '3', 'DD01');  
    INSERT INTO #Temp VALUES ('15210', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15210', '2', 'AA21');  
    INSERT INTO #Temp VALUES ('15210', '3', 'AA21');  
    INSERT INTO #Temp VALUES ('15210', '1', 'DD21');  
    INSERT INTO #Temp VALUES ('15210', '2', 'DD21');  
    INSERT INTO #Temp VALUES ('15210', '3', 'DD21');  
    INSERT INTO #Temp VALUES ('15211', '1', 'CC51');  
    INSERT INTO #Temp VALUES ('15211', '1', 'DD20');  
    INSERT INTO #Temp VALUES ('15212', '1', 'AA21');  
    INSERT INTO #Temp VALUES ('15212', '1', 'DD03');  
      
    SELECT * FROM #Temp  
    ORDER BY ID  
      
    SELECT   
     SS.ID  
    ,SS.seat  
    ,STUFF((SELECT ', ' + CAST(LTRIM(RTRIM(CR.Code)) AS VARCHAR(10)) [text()]  
            FROM #Temp CR   
            WHERE CR.ID = SS.ID and CR.seat = SS.seat  
            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Codes  
    FROM #Temp SS  
    GROUP BY SS.ID, SS.seat  
      
    --alternative method:  
    SELECT SS.ID,  
           SS.seat,  
           LEFT(cs.Codes, Len(cs.Codes) - 1) AS Codes  
    FROM   #Temp SS  
           CROSS APPLY (SELECT Code + ','  
                        FROM   #Temp CR  
                        WHERE  CR.ID = SS.ID  
                               AND CR.seat = SS.seat  
                        FOR XML PATH('')) cs (Codes)  
    GROUP  BY SS.ID,  
              SS.seat,  
              LEFT(cs.Codes, Len(cs.Codes) - 1)   
    

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

0 additional answers

Sort by: Most helpful