clsConcat sub Add optional parameters

[MarkJohnstoneGitHub]

Just check out clsConcat.cls clsConcat.Add() with the numerous optional parameters. Is that efficient? Regardless of how many optional parameters passed, I think it's always passing 10 addresses. eg Add "A","B", "C", "D" four pointers referring to strings and 6 null pointers. Which might explain comparing against .Net String Concat member they have overloads for one to four parameters for efficiency reasons.

[hecon5]

I'm not sure that it's "inefficient" per-se overall.

You could check this by using clsPerformance, adding a loop to clsConcat.add 1000 times with the as-is definition, then adding a second add function (call it add2), where the additional parameters are not there ( but copied, so you're only changing the parameter passing), and see if there's a difference.

I'm betting it's totally inconsequential.

From my own use of clsConcat, you can build humongous strings VERY quickly, like almost faster than you can iterate through a local table fast.

So, to sum up, it might not be the "best" way, but it allows you to build complex strings very easily, does it super quickly (even with inefficiencies), and doesn't require me to build a specialized concatenation tool, any one of those is a win in my book, with all the upsides, it's a no-brainer, and trying to spend time optimizing something that's already fast when I have tons of other things I'd get more mileage out of, it doesn't even nudge the meter.

[MarkJohnstoneGitHub]

To test performance I've used Stopwatch from my VBA-DotNetLib project

Just for passing parameters and calling a sub 100,000,000 times

Concat2 Ticks: 71081166
Concat4 Ticks: 69075164
Concat6 Ticks: 69592851
Concat8 Ticks: 69187944
Concat10 Ticks: 72979371
ConcatOptional 2 params Ticks: 135642710
ConcatOptional 4 params Ticks: 120586290
ConcatOptional 6 params Ticks: 108187763
ConcatOptional 8 params Ticks: 83014029
ConcatOptional 10 params Ticks: 70776272

Interesting as have more parameters for ConcatOptional subroutine its performance approaches the non-optional version. Likey what's occuring is for each optional parameter not provided a string null reference is created so the more parameters provided this overhead is reduced accordingly.

For four parameters which is probably the general use case a significant difference just for passing parameters.

Public Sub Concat2(ByRef str1 As String, ByRef str2 As String)
End Sub

Public Sub Concat4(ByRef str1 As String, ByRef str2 As String, ByRef str3 As String, ByRef str4 As String)
End Sub

Public Sub Concat6(ByRef str1 As String, ByRef str2 As String, ByRef str3 As String, ByRef str4 As String, ByRef str5 As String, ByRef str6 As String)
End Sub

Public Sub Concat8(ByRef str1 As String, ByRef str2 As String, ByRef str3 As String, ByRef str4 As String, ByRef str5 As String, ByRef str6 As String, ByRef str7 As String, ByRef str8 As String)
End Sub

Public Sub Concat10(ByRef str1 As String, ByRef str2 As String, ByRef str3 As String, ByRef str4 As String, ByRef str5 As String, ByRef str6 As String, ByRef str7 As String, ByRef str8 As String, ByRef str9 As String, ByRef str10 As String)
End Sub

Public Sub ConcatOptional(ByRef str1 As String, Optional ByRef str2 As String, Optional ByRef str3 As String, Optional ByRef str4 As String, Optional ByRef str5 As String, _
    Optional ByRef str6 As String, Optional ByRef str7 As String, Optional ByRef str8 As String, Optional ByRef str9 As String, Optional ByRef str10 As String)
End Sub

[hecon5]

I think, if If I read this doc correctly, your findings equate to this?

In that case, to make sure I'm understanding correctly, are you saying that in a case where you run this routine 100 Million times, the increase in time is only 1.7 seconds off "ideal" for the concat4 vs concat10 optional params (original)?

That seems really good, and almost not noticeable to most users.

Subroutine Name	Tick Count	Seconds	Difference from Minimum
Concat2	71081166	71.08	2.01
Concat4	69075164	69.08	0.00
Concat6	69592851	69.59	0.52
Concat8	69187944	69.19	0.11
Concat10	72979371	72.98	3.90
ConcatOptional 2 params	135642710	135.64	66.57
ConcatOptional 4 params	120586290	120.59	51.51
ConcatOptional 6 params	108187763	108.19	39.11
ConcatOptional 8 params	83014029	83.01	13.94
ConcatOptional 10 params	70776272	70.78	1.70

[MarkJohnstoneGitHub]

That's just for passing parameters. There's probably other "unnecessary" overheads could trim i.e. not having to test all ten strings for a null string. Offhand it appears a good idea thou comes at a cost. No free lunches. 😉 I haven't fully checked out the code what other things might be able to trim. Overall looks similar concept to a StringBuilder class.

One other thing stands out ReDim Preserve astrPages(0 To lngCurrentPage + 1) when get around to it check it. That's potientially a source of the biggest effect on performance. Maybe another data structure might be more appropriate.

How does the String Concat class perform verses default VBA string concatenation?

Was investigating improving the performance of string functions and checking what's about in VBA verses doing using COM Interlop. Realised probably excessive overheads passing strings via COM i.e. conversion from a BStr to .Net String etc. Case of is the performance improvements worth the time to develop solely in VBA test etc.

The project VBCorLib might have things worth checking any similar functionality required. I couldn't get it working easily in VBA as the type libraries used are 32 bit and some VB6 compatible code. Instead I'm attempting to borrow from .Net using COM Interop I might revisit it as see what can utilize in VBA.

[MarkJohnstoneGitHub]

Some interesting quick analysis from those performance figures allowing for variation in testing, there does not appear to be any clear difference between multiple parameters verses multiple calls. Also having multiple parameters with optionals can be twice as slow which are obtaining for various scenerios eg. Making AddString public so addding one string verses Add() and removing the AddString AppendOnAdd for fair comparison. Which is reflected in the performance figures above.

Performance of AddString() appears promising so the underlying methodology ok.

For when to appropriately use a string builder see Concatenating Strings Efficiently same concepts apply. Most times probably the VBA concat & is more appropriate unless fits their Rules Of Thumb.

When get around to it will also test verses other VBA string builders and the .Net StringBuilder wrapper done. The sub AddString(strAddString As String) appears approximately 30% slower then using the .Net Wrapper so underlying methodolgy appears reasonable. Have not check out other members as yet.

[bclothier]

I wonder if using ParamArray instead of multiple optionals would be better, and would allow us to to stop processing at the last element of the ParamArray rather than testing every optional parameter to see if they are non-empty string. This has the added benefit of not having to change the call sites.

[MarkJohnstoneGitHub]

Quick ParamArray test passing four parameters about 4 times slower. Then you've probably got other overheads for your string concat going from a variant to string etc.

[MarkJohnstoneGitHub]

If concerned about performance maybe "overloads" for most general use cases from say 1-4 string parameters and maybe a string array or maybe ParmArray one as well.

[MarkJohnstoneGitHub]

To cut down the overheads of testing each optional string for non-empty the "overloads" for say 1-4 parameters. If passing using ParamArray maybe using Join might be the fastest method. Join(args, VBA.vbNullString)

[bclothier]

The biggest obstacle for adopting pseudo-overloads here is that this complicates the call sites. Instead of having just Concat(....), we have to juggle between Concat2(x, y) or Concat3(x, y, z) and remember to adjust the ConcatN if we modify the code and need to add/remove parameters. That doesn't feel very ergonomic.

A halfway point might be to have ConcatStack with 2 required and 2 optional parameters, and ConcatHeap with 5 required and 5 optional parameters but I'm not even convinced that this is worth the extra trouble and whether that will optimize the same way on different architectures.

[MarkJohnstoneGitHub]

Possible another solution from Mathieu Guindon Is it possible to limit the args of a ParamArray in a VBA Class Sub or Property?.