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

[ahmedsa19051985]

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

expected result attached with post question

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

 

[fredericofonseca]

why do people keep cross posting I really never understood.

see replies to your thread here https://www.sqlservercentral.com/fo...ff-query-executing-take-long-time-as-5-minute


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[TamarGranor]

@fredericofonseca, FWIW, I don't consider posting on different forums to be cross-posting. In my view, cross-posting is posting in multiple sections of the same forum.

The reason for posting in multiple forums is that different people visit them, so you increase your odds of an answer.

Tamar

 

[Andrzejek]

Crossposting is the act of posting the same message to multiple information channels; forums, mailing lists, or newsgroups.

Whatever you call it, the issue is: people who cross-post almost never come back to state what the resolution to their problem is. In the best outcome, they just say: Problem has been solved. Which does not help others who look at the post and hope to find the resolution to their similar problem.
Also, other helpful people try to help the OP without knowing that the issue has been solved some place else.

No wonder that...

Enforcement actions against crossposting individuals vary from simple admonishments up to total lifetime bans.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TamarGranor]

I've been answering tech questions online since the late 80's. Until this group, I've never heard anyone call posting in different forums (as opposed to different sections of the same forum) "cross-posting" and I've never heard objections to it.

Certainly, I sometimes encounter the same poster with the same question in multiple forums. I've been known to reply with something like "I answered your question on XYZ forum."

Maybe my feelings about this are because I work mostly in a product with a small (and shrinking) community, so finding the right person to answer your question isn't always easy.

Tamar