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.
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