Avoid String Concatenation Inside T-SQL Loops

Recently I became aware of something that is probably obvious, it quickly became so to me in hindsight. But just in case I thought I’d share my blunder and happy discovery with the rest of the universe.

I had a task to clean up some records that had gotten duplicated in one of our system. The exact details are not important, but as part of it I had to do a loop and in the loop create a dynamic SQL query. To do that I created a big string, and did the lazy coder thing and just started typing where the cursor landed. As a result I was concatening my rather large SQL statement inside the loop. Here is a very simplified example demonstrating what I did, instead of a SQL query I’m just looping and concatenating something approximately the same size as my query was.

declare @cnt int;
declare @bigdata varchar(2000);
 
set @cnt = 1;
 
while @cnt < 1000000 begin
    set @cnt = @cnt + 1;
    set @bigdata = '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
    set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
end

On my PC this consistantly took 43 seconds to execute. Natually I started cleaning up my code to help performance, and did what I should have done to start with, moved the bulk of my concatenation outside the loop. This second example demonstrates this:

declare @cnt int;
declare @bigdata varchar(2000);
declare @moreBigData varchar(2000);
 
set @bigdata = '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
set @bigdata = @bigdata + '12345678901234567890123456789012345678901234567890'
 
set @cnt = 1;
 
while @cnt < 1000000 begin
    set @cnt = @cnt + 1;
    set @moreBigData = @bigdata;
end
 


Executing the above query on my same PC now takes 1 second. Yes, 1. It ran in 1/43rd the time. It’s easy for even an experienced developer to get in a hurry and make a basic mistake like this, which is why I’m passing it along. Sometimes it’s easy to forget the basics. When working on your long procedures, make sure to avoid string concatenation within loops as much as possible to maximize performance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s