More efficient way to concatenate long strings(varchar(max)) than "CONCAT" or " &quot-CodePudding

We are developing a software product that needs a very big amount of data and we are trying to find the most efficient way to concatenate 3 strings. We used the most known ways to concatenate the strings, such as the " " operator or the "CONCAT" function. My query will look like this, when I use the " " operator:

 declare @S1 varchar(max)
 declare @s2 varchar(max)
 declare @s varchar(max)
 declare @lni int 
 set @lni = 0 

 set @s = cast('' as varchar(max))
 set @S1 = cast('TEST' as varchar(max))
 set @s2 = cast(' OK' as varchar(max))


while (@lni <100000) begin 
   set @lni = @lni 1
   set @S =@s  @S1  @s2

 end 
 print len(@S)
 print @S

Using "Concat" function, my query will look like this:

 declare @S1 varchar(max)
 declare @s2 varchar(max)
 declare @s varchar(max)
 declare @lni int 
 set @lni = 0 

 set @s = cast('' as varchar(max))
 set @S1 = cast('TEST' as varchar(max))
 set @s2 = cast(' OK' as varchar(max))


while (@lni <100000) begin 
   set @lni = @lni 1
   set @S =concat(@S,@S1,@S2)

 end 
 print len(@S)
 print @S

The query that used the " " operator was executed by the SSMS in 2 minutes and 30 seconds and the one using "CONCAT" function was exected in a minute and 18 seconds. I also have to mention that I replaced '100000' with '10000000' and it was executed in more than 5 hours. I am curious if we can find a faster way to execute.

CodePudding user response：

Try the following non-looping equivalent using a tally-table:

 declare @s varchar(max) = '', @s1 varchar(max) = 'TEST', @s2 varchar(max) = ' OK';
 with 
    l0 as (select 0 v from (values(0),(0),(0),(0),(0))v(v)), 
    l1 as (select 1 v from l0 a cross join l0 b),
    l2 as (select 2 v from l1 a cross join l1 b),
    l3 as (select 3 v from l2 a cross join l2 b),
    l4 as (select 4 v from l3 a cross join l3 b),
    n  as (select n = Row_Number() over(order by @@spid) from l4)

    select top (100000) 
    @s = string_agg(concat(@s1, @s2),'') 
    from n;

    print @s;

In my test doing 100,000 concats takes 2 secs, I can't tell you how long your loop version takes as it hasn't finished yet.

See Demo Fiddle

CodePudding user response：

You can also use recursive CTE and FOR XML PATH

 declare @S1 varchar(max)
 declare @s2 varchar(max)
 declare @s varchar(max)
 declare @lni int 
 set @lni = 0 

 set @s = cast('' as varchar(max))
 set @S1 = cast('TEST' as varchar(max))
 set @s2 = cast(' OK' as varchar(max))
 ;

 WITH CTE AS 
 (
    SELECT concat(@S,@S1,@S2) s, @lni as i
    UNION ALL
    SELECT s,i 1 FROM CTE
    WHERE i<99999
)
Select @S = 
( 
    SELECT S AS [text()]
    FROM CTE
    FOR XML PATH ('')
)
OPTION (MAXRECURSION 0);

print len(@S)
print @S