after add feature name and feature value query execution result take too much time ?

[ahmedsalah]

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

execution plan as below

Paste The Plan - Brent Ozar Unlimited®

www.brentozar.com

expected result
same result but enhance query to be faster

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

 

[Skydiver]

Is the query plan equally as complex without the FeatureName and FeatureValue fields?