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

Question

1.00/5 (1 vote)

See more:

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

my script as below
expected result

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

execution plan

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

What I have tried:

SQL

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

Posted 30-Nov-21 17:01pm

ahmed_sa

Updated 1-Dec-21 3:48am

v2

Add a Solution

Comments

CHill60 1-Dec-21 3:11am

You haven't given us 'core_datadefinitiondetails' however, I would probably try to split out the FeatureValue and FeatureName into separate CTEs or temp tables.
The plan you have provided bears no resemblance to the code you have provided in your question which doesn't help, however I do notice several table scans - perhaps review your indexes on your tables.
I would also question why you would want to present data like this in the first place

ahmed_sa 1-Dec-21 6:05am

only can you show me how to do on cte as feature name and feature value
can you help me please

ahmed_sa 1-Dec-21 6:06am

I need to get same features name and feature values based on group by fields

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-12-01T03:48:00

1. Watch out for all the spelling mistakes with your table core_datadefinition_Details or core_datadefinition_Detailes or core_data_definition_Details - many members would not bother persisting through that to get your code to work.

2. As I mentioned in my comments, the addition of appropriate indexes will help remove the table scans in your plan e.g.

SQL

create index gen_ZfeatureKey on [dbo].[gen] ([ZfeatureKey]);
CREATE INDEX PA_PartID ON [dbo].[PartAttributes] ([PartID]);
CREATE INDEX PA_Feature ON [dbo].[PartAttributes] ([ZfeatureKey]);

3. When I was trying to pull out bits of your code I noticed that you have correlated sub-queries - see SQL server performance - Death by correlated subqueries - SQL Service[^]

4. I think you may have over-done the normalization in your table schemas - personally I would combine core_datadefinition_Details and PartAttributes into a single table

5. I suggested breaking out the code into CTEs - this was originally to make it easier for me to read, but as in point 3 above it helped me track down potential issues. In the code below cte1 addresses my point 4 and gets key information into a simpler format.
cte2 takes that information to do the STUFF and capture ConCount by PartID
The final part of the query was just going to take what I had and join it to the [gen] table to get the remaining details. but I can't determine what to join to [gen] ON - you have ZFeatureKey as the common column - but are combining the features themselves into the $ separated list

6. So that leads me to question your schema again, however this code now gets the expected results

SQL

;with cte1 as
(
	select ColumnName, FeatureValue, p.PartID, p.ZfeatureKey, Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID
	from core_datadefinition_Details d
	inner join PartAttributes p on d.ColumnNumber = p.ZfeatureKey 
	INNER JOIN gen Co ON Co.ZfeatureKey = p.ZfeatureKey
), cte2 as
(
	select PartID, Code,CodeTypeID,RevisionID,ZPLID, count(*) as ConCount, 
				stuff(( SELECT  '$' + CAST( ColumnName AS VARCHAR(300)) AS [text()]
                     FROM cte1 
                     ORDER BY cte1.ZfeatureKey
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
                         , 1,  1, '') as FeatureName,

				stuff(( SELECT  '$' + CAST( FeatureValue AS VARCHAR(300)) AS [text()]
                     FROM cte1
                     ORDER BY cte1.ZfeatureKey
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
                         , 1,  1, '') as FeatureValue
	from cte1
	Group By PartID,Code,CodeTypeID,RevisionID,ZPLID
)
select PartID, Code,CodeTypeID,RevisionID,ZPLID,ConCount, FeatureName, FeatureValue
from cte2

Doesn't have any correlated sub-queries and (courtesy of the indexes in point 2) also doesn't have any table scans in the Execution plan.

Caveat: I don't know if this will work fully in your case as one PartID is not enough data for proper testing

One final point - use the Reply link next to comments so that the member posting the comment is notified of your reply.