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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

December 1, 2021 at 3:51 am

#3958920

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

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

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

This topic was modified 2 years, 4 months ago by ahmed_elbarbary.2010.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

That is a bit of a nightmare.

Just looking through the plan, I see four different plan affecting implicit converts. I see a missing join predicate error. I also see wildly off statistics, "0 of 9190440", all over the place. Also, without a WHERE clause, you're simply moving everything. There's very little you can do to improve a query that moves the planet. You can only really upgrade the hardware.

So, I'm not even going to try to find where to start. Instead, I'd strongly suggest you take this back to the basics. Rebuild the query, one statement at a time. Go slow. Validate each step individually.

Also, check your statistics to see if they're up to date because you're getting some real mess in there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

frederico_fonseca SSCoach Points: 16014 More actions · Answer 2

adding to this - the more complex queries are also using tables without ANY index at all - so lots of table scans (HEAPS to be more precise) - so something to look at also as you should add a clustered index and any further indexes as needed to support your query operations (not just this one!!!)

regarding this bit of code

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

I may be wrong - but are you not getting the same string multiple times on the resulting field? if so and if that is not the requirement then changing this may also reduce the amount of data SQL has to store and sort.