After add two statement stuff query executing take long time as 5 minute?

Transact-SQL
, ,
1

I work on SQL server 2014 after add two stuff statement to script below

it become very slow

before add two stuff statement it take 28 second for display 500 thousand

now as below script and after add two statement stuff take 5 minutes

so how to solve issue please

my script as below :

IF OBJECT_ID('[dbo].[gen]') IS NOT NULL
 DROP TABLE [dbo].[gen]    
 IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL
 DROP TABLE [dbo].[PartAttributes]    
 IF OBJECT_ID('dbo.core_datadefinition_Detailes') IS NOT NULL
 DROP TABLE core_datadefinition_Detailes
    
    
    
    
 CREATE TABLE core_datadefinition_Detailes(
     [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     [ColumnName] [nvarchar](500) NOT NULL,
     [ColumnNumber] [int] NOT NULL,
        
  CONSTRAINT [PK_Core_DataDefinition_Details] PRIMARY KEY CLUSTERED 
 (
     [ID] ASC
 )
 )
 insert into core_datadefinition_Detailes([ColumnNumber],[ColumnName])
 values
 (202503,'Product Shape Type'),
 (1501170111,'Type'),
 (202504,'Package Family')
    
    
    
    
    
    
    
 CREATE TABLE [dbo].[gen](
     [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
     [CodeTypeID] [int] NULL,
     [RevisionID] [bigint] NULL,
     [Code] [varchar](20) NULL,
     [ZPLID] [int] NULL,
     [ZfeatureKey] [bigint] NULL,
    
 ) ON [PRIMARY]
    
    
 GO
 SET IDENTITY_INSERT [dbo].[gen] ON 
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey]) VALUES (7565,  849774, 307683692, N'8541100050', 4239, 202503)
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey]) VALUES (7566,  849774, 307683692, N'8541100050', 4239, 202504)
 INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],   [Code], [ZPLID], [ZfeatureKey]) VALUES (7567,  849774, 307683692, N'8541100050', 4239, 1501170111)
    
    
 SET IDENTITY_INSERT [dbo].[gen] OFF
    
 CREATE TABLE [dbo].[PartAttributes](
     [PartID] [int] NOT NULL,
     [ZfeatureKey] [bigint] NULL,
     [AcceptedValuesOption_Value] [float] NULL,
     [FeatureValue] [nvarchar](500) NOT NULL
 ) ON [PRIMARY]
    
 GO
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 202503, N'Discrete')
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 1501170111, N'Zener')
 INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) VALUES (413989, 202504, N'SOT')
    
    
    
    
            
    
 SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
                 stuff(( SELECT  '$' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()]
                     FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from gen C 
                     inner join core_datadefinitiondetails d with(nolock) on C.ZfeatureKey=d.columnnumber
                     INNER JOIN PartAttributes P 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.FeatureValue AS VARCHAR(300)) AS [text()]
                     FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM gen C2
                     INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey)CP2
                     where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code
                     ORDER BY CP2.ZfeatureKey
                     FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)') 
                         , 1,  1, '') as FeatureValue
                 FROM 
                 PartAttributes PM 
                 INNER JOIN    gen Co ON Co.ZfeatureKey = PM.ZfeatureKey Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID

expecting result

PartID Code CodeTypeID RevisionID ZPLID ConCount FeatureName FeatureValue
413989 8541100050 849774 307683692 4239 3 Product Shape Type$Package Family$Type Discrete$SOT$Zener
2

Code doesn't work and there is not enough data to get real execution plan.

3

THANK YOU FOR support

i solve issue by

SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
 LEFT(cs.FeatureName, Len(cs.FeatureName) - 1) AS FeatureName,
 LEFT(cs2.FeatureName2, Len(cs2.FeatureName2) - 1) AS FeatureName2
 FROM 
 PartAttributes PM 
 INNER JOIN    gen Co 
 ON Co.ZfeatureKey = PM.ZfeatureKey 
 CROSS APPLY (SELECT   CP.ColumnName  + '$'
 FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey 
 from gen C 
 inner join core_datadefinition_Detailes d with(nolock) 
 on C.ZfeatureKey=d.columnnumber
 INNER JOIN PartAttributes P on P.partid=PM.partid)CP
 where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
 ORDER BY CP.ZfeatureKey
 FOR XML PATH('')) cs (FeatureName)
 CROSS APPLY (SELECT   CP2.FeatureValue + '$'
 FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey 
 FROM gen C2
 INNER JOIN PartAttributes P 
 on C2.ZfeatureKey=P.ZfeatureKey)CP2
 where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code
 ORDER BY CP2.ZfeatureKey
 FOR XML PATH('')) cs2 (FeatureName2)
 Group By PartID,Code,Co.CodeTypeID,Co.RevisionID
 ,Co.ZPLID,cs.FeatureName,cs2.FeatureName2