Concatenate fields in a more performative way

Question

Concatenate fields in a more performative way

Navigation

#1 by (4 votes)
#2 by (4 votes)

6

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'll do it with a table with some 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 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 of doing this in SQL SERVER and how.

sql sql-server

asked by anonymous 12.03.2016 / 01:03

2 answers

4

Another way would be to use STUFF it is 4 times faster than using COALESCE, I do not know of 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

12.03.2016 / 11:58

What is the purpose of automatically generated directories in an ASP.NET MVC project? How important is Integrity and CrossOrigin attributes?

score 4 · Accepted Answer

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

But there's an even faster way to do that by using GROUP_CONCAT . But I think the effort is not worth it, since the performance difference is minimal.