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.

Execute a query on ALL databases? sp_MsForEachDB might do the trick!

Ever wanted to execute a query on ALL databases available on 1 server?

sp_msforeachdb @command1 = '

select  *

from    [?].sys.database_files

order by name'

the question mark will be replaced with the database name, so generating some SQL for each DB that exists will be easy. I used this one to shrink some large transaction logs




if object_id('##tmp_sqlstatements') is not null

begin

  drop table ##tmp_sqlstatements

end

create table ##tmp_sqlstatements (

  Id int identity(1, 1)

, SqlStatement nvarchar(max)

)

go

sp_msforeachdb @command1 = '

insert

into    ##tmp_sqlstatements

select  ''use [?];

          dbcc shrinkfile('''''' + name + '''''')''

from    [?].sys.database_files

where   type_desc = ''log''

order by name'

declare @totrow int

      , @currow int

      , @nsql nvarchar(max)

select  @totrow = count(Id)

      , @currow = 1

from    ##tmp_sqlstatements

while @currow <= @totrow

begin

  select  @nsql = SqlStatement

  from    ##tmp_sqlstatements

  where   Id = @currow

 

  exec sp_executesql @nsql

  set @currow = @currow + 1

end

drop table ##tmp_sqlstatements

It executes all the statements at once, so it can be used as a part of a maintenance plan. If creating more disk space is the issue for executing this script, make sure that you put the recovery mode into simple. But of course only when you don't need the transaction logs!!

This is very easy to do :

select  replace('alter database [[NAME]] set recovery simple ', '[NAME]', name)

from    sys.databases

where   recovery_model_desc = 'full'

order by name

This will give a result of SQL statements to execute.

Create temp tables with dynamic sql

Recently i had to create some temp tables in some stored procedure just to make sure i could process a lot of inserts within 1 set (from the temp table) from another stored procedure.

as always i ran into the problems regarding scoping.... a temp table which is created with only with one # character in front is only known in it's current session.. a temp table created with ## is server wide accessible.