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

ahmedsalah

Member
Joined
Sep 26, 2018
Messages
24
Programming Experience
3-5
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:

after add two stuff for feature name and feature value query run take long time:
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

expected result
same result but enhance query to be faster

PartIDCodeCodeTypeIDRevisionIDZPLIDConCountFeatureNameFeatureValue
413989​
8541100050​
849774​
307683692​
4239​
3​
Product Shape Type$Package Family$TypeDiscrete$SOT$Zener
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
5,083
Location
Chesapeake, VA
Programming Experience
10+
Is the query plan equally as complex without the FeatureName and FeatureValue fields?
 
Top Bottom