How to change STRING_AGG to stuff xml to split feature value by pip?

Question

I work on sql server 2019 . i can't write query below with stuff for xml .

so how to change STRING_AGG to stuff xml ?

query below take too much time so i need to try

with stuff for xml to reduce time cost .

query i try it

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   
inner join ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1  on f1.partid = a.OrignalPartId  
inner join [Technology].Receipe Ft  on ft.featureid = f1.featureid and ft.operatorid = 1  
group by a.RecomendationId  

ddl tables structures

create table ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg  
      (  
      ID int identity(1,1),  
      PartId int,  
      FeatureID int,  
      FeatureName varchar(200),  
      FeatureValue varchar(200)  
      )  
  
create table extractreports.dbo.partsrecomendationActive  
(  
RecomendationId int identity(1,1),  
OrignalPartId int  
  
)  
  
CREATE TABLE [Technology].[Receipe](  
    [ReceipeID] [int] IDENTITY(1,1) NOT NULL,  
    [FeatureID] [int] NULL,  
    [OperatorID] [int] NULL,  
PRIMARY KEY CLUSTERED   
(  
    [ReceipeID] ASC  
))  

expected result

Answer 1

Hi @ahmed salah

how to change STRING_AGG to stuff xml

Check this:

;WITH CTE AS  
(  
 select a.RecomendationId, cast(f1.FeatureValue as varchar(300)) AS FeatureValue,f1.featureid  
 from extractreports.dbo.partsrecomendationActive a   
 inner join ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1  on f1.partid = a.OrignalPartId  
 inner join [Technology].Receipe Ft  on ft.featureid = f1.featureid and ft.operatorid = 1  
)  
SELECT A.RecomendationId  
      ,STUFF((SELECT  '|' + FeatureValue FROM cte B where B.RecomendationId=A.RecomendationId  
              ORDER BY FeatureId ASC FOR XML PATH('')),1,1,'') AS DiffFeatures  
FROM CTE A    
GROUP BY A.RecomendationId  

Best regards,
LiHong

Answer 2

Since it is a perf issue we're not going to be a lot of help since we don't have the large dataset to notice the perf issue. Have you run the profiler to determine where it is taking the most time?

I notice in your query you're doing casting. You don't need any of that so remove them. This will help a little bit. Also verify your joins are using indexed (Or FK relationship) columns. Otherwise that is going to impact the performance.

   select  
      a.RecomendationId,  
      STRING_AGG(f1.FeatureValue ,'|') WITHIN GROUP (ORDER BY f1.FeatureId ASC) AS DiffFeatures  
    from @partsrecomendationActive a   
    inner join @TechnologyPlPartsFeaturValuesOrg f1  on f1.partid = a.OrignalPartId  
    inner join @Receipe Ft  on ft.featureid = f1.featureid and ft.operatorid = 1  
    group by a.RecomendationId  

Note: I'm using a table variable so ignore the table names.

I also think your STRING_AGG could probably be converted to a regular group by on a subselect (or join or CTE) and then have the higher level select STRING_AGG. But without testing on a large dataset then it would be hard to tell if it impacted the performance. The profiler will tell you that.

Answer 3

query below take too much time so i need to try

with stuff for xml to reduce time cost .

The likelihood that the FOR XML klduge would be faster is about nil. The reason query is slow is elsewhere.

Upload the query plan on www.pastetheplan.com, so we have something to work from.