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

[ahmedsaazizba]

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

execution plan

Paste The Plan - Brent Ozar Unlimited®

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

expected result

[r937]

i’m sorry that no one has replied… it is likely that no one can help you

i looked at this –

@ahmedsaazizba

https://www.brentozar.com/pastetheplan/?id=HkXSLJQKt

and i truly lack the ability to understand it, never mind figure out what’s wrong with it

sorry

[DaveMaxwell]

OK, I needed a mental break and a puzzle to solve, so I took a crack at this…

This query seems…overcomplicated. What were you trying to accomplish with all the casts?

To be honest, I opened your Paste the Plan, and…no. Too much information that didn’t seem to have any bearing on the question posted here.

SQL Fiddle | A tool for easy online testing and sharing of database problems...

I took your DDL structure over to SQL Fiddle and played with it a bit. But here is the end result of the query I came up with…

SELECT DISTINCT PartId
     , C.Code
     , C.CodeTypeID
     , C.RevisionID
     , C.ZPLID
     , FeatureKey = STUFF((SELECT '$' + d.ColumnName 
                             FROM Gen G
                            INNER JOIN core_datadefinition_Detailes d with(nolock) on G.ZfeatureKey = d.columnnumber
                            ORDER BY G.ZfeatureKey
                              FOR XML PATH ('')), 1, 1, '')
     , FeatureValue = STUFF((SELECT '$' + FeatureValue 
                               FROM PartAttributes QPA
                              WHERE QPA.PartID = PA.PartID
                              Order BY ZFeatureKey
                                FOR XML PATH ('')), 1, 1, '')
  FROM gen C 
  INNER JOIN PartAttributes PA ON C.ZFeatureKey = PA.ZFeatureKey

Which gives the same results as what your original query…

Your query on SQL fiddle for comparison

The difference is more noticeable with a second part (oh, and FYI, your query breaks on a second part as it doesn’t return the correct part attribute values…)