ahmedsa2018
Programmer
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 :
Link
statment that make issue
issue is statment execution take too much time to execute
ddl with indexes
so How to reduce it please
additionally it take too much time to execute it .
it take 6:06 minutes
execution plan as below :
Link
statment that make issue
SQL:
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
issue is statment execution take too much time to execute
ddl with indexes
SQL:
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
))