String Concatenation in SQL Server. Concat vs REPLACE

Every now and then i get to see some T-SQL code where you get that feeling that something could get a performance upgrade, while not being sure if it would benefit in a positive way. I was going through a lot of code today and saw some lines which made me think.


In pseudo code i had the following line :

set @variable_a = 'First part of a string, with ''' + @variable_b + ''' concatenated to it'

Straightforward, it concatenates a string to another string. How many times you that? The code i went through was full with these kind of concatenations. 

Every time i see a string concatenation, i am thinking about the String vs StringBuilder example in .Net code, where the StringBuilder is much faster in concatenations. So, maybe there is a same general rule for this in SQL Server.

With that in mind, what about the REPLACE function. I use it very often, and to be honest, i used it mainly to make my code more readable. If you think about dynamically build up sql, a placeholder to be replaced reads better then a string concatenation.

So, time to put this to a test and see who is the David that brings down Goliath.  And i made the following script to see which one is faster, and which is slower.

------------------------------------------

-- String CONCATENATION

------------------------------------------

declare @totrow int

      , @teststring varchar(max)

      , @startdate datetime

      , @enddate datetime

set @startdate = getdate()

set @teststring = ''

set @totrow = 1000

while @totrow > 0

begin

 

  set @teststring = @teststring + 'aa'

 

  set @totrow = @totrow - 1

end

set @enddate = getdate()

print 'String CONCATENATION : ' +

        cast(datediff(ms, @startdate, @enddate) as varchar(10)) +

        ' ms'

------------------------------------------

-- String REPLACE

------------------------------------------

set @startdate = getdate()

set @teststring = '[A]'

set @totrow = 1000

while @totrow > 0

begin

  set @teststring = replace(@teststring, '[A]', 'aa[A]')

  set @totrow = @totrow - 1

end

set @teststring = replace(@teststring, '[A]', '')

set @enddate = getdate()

print 'String REPLACE       : ' +
        cast(datediff(ms, @startdate, @enddate) as varchar(10)) +

        ' ms'

The changing part in the 2 blocks of code is the REPLACE functionality. When i run this code, i see that the first part is taking up approximately 0 ms and approximately 70ms for the replace part.And that is with ONLY 1000 replacements. Try to guess what happens when we give the total rows 

variable a value of 10000.

The concatenation part then is using 230 ms and the REPLACE is taking up to 5100 ms to process.

So, next time you use a REPLACE function, use is wisely. On lower rates it can be used, 

higher numbers could end up in useless performance losses.