2

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

Final result:

final result after add two stuff

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
abeer shlby
  • 41
  • 6
  • 1
    For performance related questions please provide the execution plan using "Paste The Plan". – Dale K Dec 01 '21 at 04:30
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Dec 01 '21 at 04:31
  • https://www.brentozar.com/pastetheplan/?id=HkXSLJQKt – abeer shlby Dec 01 '21 at 04:42
  • Any chance you can remove `distinct` from `SELECT distinct d.ColumnName` and `SELECT distinct P.FeatureValue`? – Alex Dec 01 '21 at 04:58
  • why remove distinct i put distinct to remove duplicate also because i can't order by feature key – abeer shlby Dec 01 '21 at 06:31

0 Answers0