Concatenate fields in a more performative way

Question

I am concatenating the description lines of a record so that instead of having N records for a description it has only one. I can make it happen, but I wanted a more performative way, because I’m going to do this with a table with about 900,000 records.

A test record created in the temporary table #T:

SELECT * INTO #T FROM (
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 1 SEQUENCIA, 'ESTE É UM EXEMPLO QUE' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 2 SEQUENCIA, 'EU ESTOU ESCREVENDO NA MÃO' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 3 SEQUENCIA, 'PARA PODER TES-' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 4 SEQUENCIA, 'TAR ESSE TROÇO QUE FIZERAM' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 5 SEQUENCIA, 'EM UM SISTEMA LEGADO E QUE' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 6 SEQUENCIA, 'EU QUERIA DESCOBRIR O MOTIVO DE TEREM LIMITADO ESSE CAM-' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 7 SEQUENCIA, 'PO A 80 CARACTERES SE É UMA DESCRICAO GIGANTE.' DESCRICAO )T

Currently to make this process I am using the COALESCE:

SELECT  
    TIPO
    ,SUBTIPO
    ,ITEM
    ,CHAVE
    ,COALESCE(
        (SELECT CAST(DESCRICAO AS VARCHAR(MAX)) + ' ' AS [text()]
         FROM #T AS O
         WHERE O.CHAVE  = C.CHAVE
         ORDER BY CHAVE,SEQUENCIA
         FOR XML PATH(''), TYPE).value('.[1]', 'VARCHAR(MAX)'), '') AS DESCRICAO
         into #xablau
FROM #T AS C
GROUP BY TIPO,SUBTIPO,ITEM,CHAVE


--SELECT * FROM #xablau

I want to know if there is a more performative way to do this in SQL SERVER and how.

Answer 1

Another way would be to use the STUFF it is 4 times faster than using COALESCE, I do not know other ways to do it more performatively than these two.

SELECT * INTO #T FROM (
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 1 SEQUENCIA, 'ESTE É UM EXEMPLO QUE' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 2 SEQUENCIA, 'EU ESTOU ESCREVENDO NA MÃO' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 3 SEQUENCIA, 'PARA PODER TES-' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 4 SEQUENCIA, 'TAR ESSE TROÇO QUE FIZERAM' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 5 SEQUENCIA, 'EM UM SISTEMA LEGADO E QUE' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 6 SEQUENCIA, 'EU QUERIA DESCOBRIR O MOTIVO DE TEREM LIMITADO ESSE CAM-' DESCRICAO UNION 
SELECT 2 TIPO, 17 SUBTIPO, 1 ITEM,  6546546546546544 CHAVE, 7 SEQUENCIA, 'PO A 80 CARACTERES SE É UMA DESCRICAO GIGANTE.' DESCRICAO )T


SELECT T1.TIPO, T1.SUBTIPO, T1.ITEM,
        STUFF(  
        (  
        SELECT ' ' + T2.DESCRICAO  
        FROM #T T2  
        WHERE T1.CHAVE = T2.CHAVE  
        FOR XML PATH ('')  
        ),1,1,'')  
FROM #T T1  
GROUP BY T1.TIPO, T1.SUBTIPO, T1.ITEM, T1.CHAVE

Answer 2

According to this article, the method it uses STUFF is 4 times faster than using COALESCE.

But there is an even faster way that is using the GROUP_CONCAT. But I believe the effort is not worth it, since the difference in performance is minimal.