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