Optimizing String Concatenation in VBA

re: Optimizing String Concatenation in VBA

Nate Oliver — Tue, 02 Jun 2009 16:12:20 GMT

That might be true if I was suggesting that my example be used in all cases, and it was just an example, and I think it could be more realistic than you appear to think.

But you did note the following?

"So, often times it makes a lot more sense, performance-wise, to work with Arrays, whether it's String & Byte Arrays, which directly coerce, or Array->String via Join() - with a buffered, 1D Array."

Your example, while trivial code, requires a different approach, so change faster() to this:

Sub Faster(strVal() As String)

Dim tmpStr As String

Let tmpStr = Join(strVal, vbNullString)

End Sub

This essentially does the same thing as slower(), and is on the order of ~100% faster, on my machine.

I'm not quite following this comment:

"It's fastest to allocate the largest possible buffer up front (all free system memory?), but you waste system resources that way."

I get the part about largest possible buffer, as otherwise there'd be overhead to moniter where you're at in the buffer and adjust it, otherwise. But I'm not sure about all free system memory?

I doubt you're going to chew up that much memory building a buffered String in VBA?

re: Optimizing String Concatenation in VBA

fzz — Tue, 02 Jun 2009 07:02:57 GMT

You do realize that your example isn't realistic? If not, replace your statements

strVal(0) = 0: strVal(1) = 1: strVal(2) = 2

strVal(3) = 3: strVal(4) = 4: strVal(5) = 5

with

strVal(0) = "a"

strVal(1) = "bb"

strVal(2) = "ccc"

strVal(3) = "dddd"

strVal(4) = "eeeee"

strVal(5) = "ffffff"

convert your faster and slower routines to functions, and check the respective function results. Your slower routine at least produces the correct result, "abbcccddddeeeeeffffff". Your faster routine produces the incorrect result "abcdef". It's easy to produce wrong answers quickly. Most people don't even need code to do that.

It takes a fair amount of nonobvious overhead code to handle preallocated buffers that don't eat too much memory. The technique is discussed in Kernighan & Pike, 'The Practice of Programming', chapter 4. In brief, you start off with a moderately large buffer, and when you need to grow the buffer, you double it. You need to keep track of the buffer size and how much of the buffer has been used.

It's fastest to allocate the largest possible buffer up front (all free system memory?), but you waste system resources that way.