Performance: String Concatenation in SQL Server

String concatenation in SQL Server can be pretty quick but under certain circumstances it can really slow down. Something to be aware of if you are working with larger varchar(max) values that need to be appended to using concatenation in TSQL.

The experiment:

Using a VARCHAR(MAX) variable, start with it empty, then in a loop concatenate another string to it using the + concatenation operator. The other string is part of a sentence that contains 64 characters. Nothing magic about the 64 characters, just a random sentence that turned out to be 64 characters long.

WHILE @loop < @maxConcats
BEGIN
	SET @loop = @loop + 1;
	SET @bigString = @bigString + @textToAdd;
END

Next test that loop with varying number of iterations, ranging from 2000 times through the loop up to 50,000 times through the loop. Keeping in mind that each time through the loop we are extending the length of the @bigString variable by 64 characters.

What this showed is that the longer the VARCHAR(MAX) variable gets the more time it takes to append text to it using the + concatenation operator. Initially concatenating with the + operator is very fast for strings that has lengths in the 2000 to 8000 times 64 characters in length or 128,000 characters to 512,000 characters. For these shorter strings concatenation takes around a millisecond or less.

then as we get into the larger string length, the concatenation time really slows down, in the range of 30 to 40 milliseconds, and linearly increasing based on the length of the string.

This test was run on SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 and SQL Server 2019 with similar results, with minor differences based on the specific VM’s we were using.

What about the CONCAT function?

I repeated the same test using the CONCAT function introduced in SQL Server 2012, and found the numbers interesting.

WHILE @loop < @maxConcats
BEGIN
	SET @loop = @loop + 1;
	SET @bigString = CONCAT(@bigString, @textToAdd);
END

What was interesting about these numbers associated with CONCAT is that they were much higher (ie much slower) than using the +, and the with CONCAT there was a bit of a swoop in the chart rather than being purely linear.

CONCAT can take as much as a second per concatenation compared to the plus taking at most in our test about 45 milliseconds.

Here is the chart with both + and CONCAT combined, with the upper orange line being the time associated with CONCAT, and the blue line being the time associated with using + for concatenation.

It makes you think a bit about using CONCAT versus +, does it.

The full test code

The following is the code that I ran on SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 and SQL Server 2019.

SET NOCOUNT ON;
DECLARE @textToAdd VARCHAR(max) = 'We the People of the United States, in Order to form a more ...'; 
DECLARE @startTime DATETIME2 = GETDATE();
DECLARE @endTime DATETIME2;

DECLARE @diff INTEGER = 0;
DECLARE @bigString VARCHAR(max) = '';
DECLARE @loop INTEGER = 0;
DECLARE @highestTestPass INTEGER = 50000;
DECLARE @maxConcats INTEGER = 0;

	
DROP TABLE #results ;
CREATE TABLE #results 
(
	testType VARCHAR(100),
	concats INTEGER,
	durationMiliseconds INTEGER
);



-- testing + for concatenation
SET @maxConcats = 0;
WHILE @maxConcats < @highestTestPass 
BEGIN
	SET @maxConcats = @maxConcats + 2000;
	SET @bigString = '';
	SET @loop = 0;
	
	WHILE @loop < @maxConcats
	BEGIN
		SET @loop = @loop + 1;
		SET @bigString = @bigString + @textToAdd;
	END

	SET @endTime = GETDATE();
	SET @diff = datediff(MILLISECOND, @startTime, @endTime);

	print concat('Difference ', @diff, '  for ', @loop, ' + concatenations');
	INSERT INTO #results VALUES ('+', @loop, @diff);
END

SELECT * , durationMiliseconds * 1.0 / concats as milisecondsPerConcat
FROM #results;





-- testing CONCAT statement instead of +
SET @maxConcats = 0;
WHILE @maxConcats < @highestTestPass 
BEGIN
	SET @maxConcats = @maxConcats + 2000;
	SET @loop = 0;
	WHILE @loop < @maxConcats
	BEGIN
		SET @loop = @loop + 1;
		SET @bigString = CONCAT(@bigString, @textToAdd);
	END

	SET @endTime = GETDATE();
	SET @diff = datediff(MILLISECOND, @startTime, @endTime);
	print concat('Difference ', @diff, '  for ', @loop, ' CONCAT concatenations');
	INSERT INTO #results VALUES ('CONCAT', @loop, @diff);
END


SELECT * , durationMiliseconds * 1.0 / concats as milisecondsPerConcat
FROM #results;

Summary

As we all know, actual results may vary, and this was a very specific test case. I would certainly not recommend avoiding the CONCAT function. I would however keep it in mind when performance tuning string concatenation, especially with larger strings.