Click here to Skip to main content
15,881,380 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on sql server 2019 i face issue i can't reduce high cost of sort it

reach to 86 percent

so How to reduce it please

additionally it take too much time to execute it .

it take 6:06 minutes

execution plan as below :

Paste The Plan - Brent Ozar UnlimitedĀ®[^]
statment execution take too much time to execute

ddl with indexes

create table ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg
       (
       ID int identity(1,1),
       PartId int,
       FeatureID int,
       FeatureName varchar(200),
       FeatureValue varchar(200)
       )
    
       ALTER TABLE ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg
 ADD CONSTRAINT PK_TechnologyPlPartsFeaturValuesOrg PRIMARY KEY (ID);
 create index partidoriginalParts_ix on ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg(partid)
 create index FlagRecomendationorg_ix on ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg(featureid,FeatureValue)include(partid)
    
 create table extractreports.dbo.partsrecomendationActive
 (
 RecomendationId int identity(1,1),
 RecomendationPartId int,
 OrignalPartId int
    
 )
    
 create clustered index recomendations_ix on extractreports.dbo.partsrecomendationActive(RecomendationId)
    
 create nonclustered index recomendationsparts_ix on extractreports.dbo.partsrecomendationActive(RecomendationPartId)
    
 create nonclustered index recomendationsoriginal_ix on extractreports.dbo.partsrecomendationActive(OrignalPartId)
    
 CREATE TABLE [Technology].[Receipe](
     [ReceipeID] [int] IDENTITY(1,1) NOT NULL,
     [PLID] [int] NULL,
     [FeatureID] [int] NULL,
     [OperatorID] [int] NULL,
     [FeatureTypeID] [int] NULL,
 PRIMARY KEY CLUSTERED 
 (
     [ReceipeID] ASC
 ))


What I have tried:

statment that make issue

select a.RecomendationId,cast(STRING_AGG(cast(f1.FeatureValue as varchar(300)) ,'|')  WITHIN GROUP(ORDER BY f1.FeatureId ASC)as varchar(300))  AS DiffFeatures into ExtractReports.dbo.TechnologyOriginalFeaturesEqual from extractreports.dbo.partsrecomendationActive a with(nolock)
 inner join ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1 with(nolock) on f1.partid=a.OrignalPartId
 inner join [Technology].Receipe Ft WITH(NOLOCK) on ft.featureid=f1.featureid and ft.operatorid=1
 group by a.RecomendationId
Posted
Comments
[no name] 10-Sep-22 11:21am    
The "query optimizer" is not a supreme being. You start with a simple query and build on it incrementally. That's how you determine performance issues before constructing a query that the "optimizer" can't deal with. You also make no mention of "record counts"; as if they are somehow immaterial to the discussion (they are not).

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900