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
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
issue is 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
))
Anandu G NathPosted Jan 24, 2024, 6:56 AM
Please check execution plan and otimize the database
Rajanikant HawaldarPosted Oct 7, 2022, 5:11 AM
aggregating 65 million rows takes time and it is highly dependent on your hardware. You can't always make a query faster than it is.
ahmed salahPosted Sep 12, 2022, 7:11 AM
yes
Asma KhalidPosted Sep 12, 2022, 5:23 AM
Have your table being indexed cluster & non-cluster?
Thanks & Regards,
Asma Khalid
For More Visit: www.asmak9.com or www.bytezaar.com