Sql Query With FOR XML

Question

I have a query

SELECT Brand,
        UEI_ID,
        Concat_Prd_Id = Stuff((
                        SELECT ',' + CAST(Second_ID AS VARCHAR(max))
                        FROM Temp1 t2
                        WHERE t1.Brand = t2.Brand
                                AND t1.UEI_ID = t2.UEI_ID
                        FOR XML Path('')
                        ), 1, 1, '')
FROM Temp1 t1
GROUP BY Brand,
        UEI_ID 

Which is taking 8.48 Min to executing 26,000 Records.

Please let me know how to reduce the time ... ?

Answer 1

Firstly add an index, (depeneding on other queries you use that reference the table) something like the below should do it:

CREATE NONCLUSTERED INDEX IX_Temp1 ON Temp1 (Brand, UEI_ID) INCLUDE Second_ID

Although I don't think it is applicable to your situation since Second_ID is probably an integer, and it won't improve execution time, it is good habit to use the XML value to account for concatenating strings with special characters in.

e.g.

SELECT Brand,
        UEI_ID,
        Concat_Prd_Id = STUFF((
                        SELECT ',' + CAST(Second_ID AS VARCHAR(MAX))
                        FROM Temp1 t2
                        WHERE t1.Brand = t2.Brand
                                AND t1.UEI_ID = t2.UEI_ID
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)', 1, 1, '')
FROM Temp1 t1
GROUP BY Brand,
        UEI_ID 

Answer 2

If you haven't add an Index on BRAND and UEI_ID, you can alter the table by using this syntax.

ALTER TABLE tablename ADD INDEX indexName (columnName);

So in your case, you can do this

ALTER TABLE Temp1 ADD INDEX IDX_Brand (Brand);
ALTER TABLE Temp1 ADD INDEX IDX_UEI_ID (UEI_IDd);