SQL Query takes a lot of time with the STUFF function

Question

Dataset example:

ID seat code 15098 1 AA21 15098 2 AA21 15105 1 AA21 15105 1 DD15 15105 1 NN60 15196 1 AA21 15196 2 DD50 15196 2 DD51 15209 1 AA21 15209 3 AA21 15209 2 CC50 15209 1 DD01 15209 3 DD01 15210 1 AA21 15210 2 AA21 15210 3 AA21 15210 1 DD21 15210 2 DD21 15210 3 DD21 15211 1 CC51 15211 1 DD20 15212 1 AA21 15212 1 DD03 

and the desired result:

 ID seat Codes 15098 1 AA21 15098 2 AA21 15105 1 AA21, DD15, NN60 15196 1 AA21 15196 2 DD50, DD51 15209 1 AA21, DD01 15209 2 CC50 15209 3 AA21, DD01 15210 1 AA21, DD21 15210 2 AA21, DD21 15210 3 AA21, DD21 15211 1 CC51, DD20 15212 1 AA21, DD03 

---

Example data table query:

 CREATE TABLE #Temp( ID varchar(50), seat varchar(50), code varchar(150)) INSERT INTO #Temp VALUES ('15098', '1', 'AA21'); INSERT INTO #Temp VALUES ('15098', '2', 'AA21'); INSERT INTO #Temp VALUES ('15105', '1', 'AA21'); INSERT INTO #Temp VALUES ('15105', '1', 'DD15'); INSERT INTO #Temp VALUES ('15105', '1', 'NN60'); INSERT INTO #Temp VALUES ('15196', '1', 'AA21'); INSERT INTO #Temp VALUES ('15196', '2', 'DD50'); INSERT INTO #Temp VALUES ('15196', '2', 'DD51'); INSERT INTO #Temp VALUES ('15209', '1', 'AA21'); INSERT INTO #Temp VALUES ('15209', '3', 'AA21'); INSERT INTO #Temp VALUES ('15209', '2', 'CC50'); INSERT INTO #Temp VALUES ('15209', '1', 'DD01'); INSERT INTO #Temp VALUES ('15209', '3', 'DD01'); INSERT INTO #Temp VALUES ('15210', '1', 'AA21'); INSERT INTO #Temp VALUES ('15210', '2', 'AA21'); INSERT INTO #Temp VALUES ('15210', '3', 'AA21'); INSERT INTO #Temp VALUES ('15210', '1', 'DD21'); INSERT INTO #Temp VALUES ('15210', '2', 'DD21'); INSERT INTO #Temp VALUES ('15210', '3', 'DD21'); INSERT INTO #Temp VALUES ('15211', '1', 'CC51'); INSERT INTO #Temp VALUES ('15211', '1', 'DD20'); INSERT INTO #Temp VALUES ('15212', '1', 'AA21'); INSERT INTO #Temp VALUES ('15212', '1', 'DD03'); 

I use the query below as part of my SSRS report stored procedure to get the result, but it has run too long since my original dataset is over 100,000 rows. Is there any other effective way to get the result.

 SELECT SS.ID ,SS.seat ,STUFF((SELECT ', ' + CAST(LTRIM(RTRIM(CR.Code)) AS VARCHAR(10)) [text()] FROM #Temp CR WHERE CR.ID = SS.ID and CR.seat = SS.seat FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Codes FROM #Temp SS GROUP BY SS.ID, SS.seat 

Answer 1

Try using Cross Apply instead of Correlated Sub-Query

 SELECT SS.ID, SS.seat, LEFT(cs.Codes, Len(cs.Codes) - 1) AS Region FROM #Temp SS CROSS APPLY (SELECT Code + ',' FROM #Temp CR WHERE CR.ID = SS.ID AND CR.seat = SS.seat FOR XML PATH('')) cs (Codes) GROUP BY SS.ID, SS.seat, LEFT(cs.Codes, Len(cs.Codes) - 1) 

Also create Non clustered index on ID and seat , including code

 CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID ON #Temp (ID, seat) include (code) 

Execution plan

Your request

Using Cross Apply