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

Question

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

Answer 1

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".