After adding Feature Name and Feature Value query takes more than 5 minutes

Question

I work on SQL Server 2014 and my issue occurred after displaying Feature Name and Feature Value separated by $.

When executing the query below after adding Feature Name and Feature Value with stuff it became very slow.

How to enhance it?

Before adding the two stuff statements it took 28 seconds to display 750 thousand records. Now as below script and after adding two stuff statements take 5 minutes.

Script below give me expected result but issue is performance is very slow.

So can I do separate Feature Name and Feature Value to make it faster? Separated by $ if possible.

My script:

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 INTO [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey]) 
VALUES (7565, 849774, 307683692, N'8541100050', 4239, 202503)

INSERT INTO [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey]) 
VALUES (7566, 849774, 307683692, N'8541100050', 4239, 202504)

INSERT INTO [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 INTO [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) 
VALUES (413989, 202503, N'Discrete')

INSERT INTO [dbo].[PartAttributes] ([PartID], [ZfeatureKey],  [FeatureValue]) 
VALUES (413989, 1501170111, N'Zener')

INSERT INTO [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

Final result:

final result after add two stuff