SQL Query taking long time with STUFF function

Question

Example Dataset:

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 Desired Result is:

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 Dataset 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 am using below query as a part of my SSRS report stored procedure to get the result but it is taking too long to run as my original dataset is more than a 100000 rows. Is there any other efficient 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 a Non clustered index on ID and seat including code

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

Execution Plan

Your query

Using Cross Apply