Concatenating is not very efficient in VBS, especially when a large number of small string are concatenated. Here is how to speed it up
style='font-size:11.0pt'>Speeding up
String Concatenation
style='mso-bidi-font-size:12.0pt'>When you dynamically want to generate an
output page using information from a table, you could use the following
function to perform this operation.
style='font-size:13.0pt'>Method 1
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>Function TableContent(…)
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim strContent
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>…
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Do While Not recordset.EOF
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>StrContent = strContent &
recordset.Fields([cell])
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>…
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Loop
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>TableContent = strContent
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>End Function
style='mso-bidi-font-size:12.0pt'>Method 1 is not an efficient if the number of
concatenations are large, since the concatenating operation <strContent =
strContent & recordset.Fields([cell])> copies strContent and
recordset.Fields([cell]) to a temporary space in addition to a copy of the
result. The total number of copied characters is in the order of the
result-string length times the number of concatenations.
style='mso-bidi-font-size:12.0pt'>In VB this problem can be avoided by using
the mid$ function to replace a part of a long empty string
<mid$(strContent,…)=[newstr]>, but in VBS this mid function cannot be
used this way.
style='mso-bidi-font-size:12.0pt'>In VBS the Method 2 can be used with the same
end result. In this code part(index)
will be at least 30*2^i bytes long. (Since strContent has 30 elements, the
function will work until the maximum length of a string is reached. Currently
this is over 2GB)
style='mso-bidi-font-size:12.0pt'>It can be easily verified that every
character is part of a concatenation operation only max(index) times.
style='mso-bidi-font-size:12.0pt'>Therefore, the total number of copied
characters due to concatenations will be in the order of the length of the
result string times the logarithm of the result sting length (strict
mathematical proof is difficult, since the length of the new-added strings may
vary). The total number of concatenations will be the same this way, but since
short string concatenations occur frequently, while long string concatenations
occur less often, the total length of all concatenated strings decreases.
style='font-size:13.0pt'>Method 2
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>Function TableContent(…)
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim strContent(30)
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>…
face="Courier New">‘color=lime face="Courier New">sub to clear the content stringface="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>ClearString strContent
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Do While Not recordset.EOF
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> color=lime face="Courier New">‘add to the result stringface="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>AddString strContent,
recordset.Fields([cell])
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>…
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Loop
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>TableContent = fnReadString(strContent)
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>End Function
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>‘face="Courier New">The
following subs and functions need to be includedface="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>Sub ClearString(part)
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim index ‘as
integer
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>For index = 0 to 30
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>part(index) = “”
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Next
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>End Sub
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>Sub AddString(part, newString)
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim tmp ‘as
string
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim index ‘as
integer
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>part(0) = part(0) & newString
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>If Len(part(0)) > 30 Then
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>index = 0
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>tmp = “”
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Do
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:2'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>tmp = part(index) & tmp
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:2'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>part(index) = “”
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:2'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>index = index + 1
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Loop until part(index) = “”
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>part(index) = tmp
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>End If
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>End Sub
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>Fucolor=blue face="Courier New">nction fnReadString(part)
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim tmp ‘as
string
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Dim index ‘as
integer
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>tmp = “”
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>For index = 0 to 30
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>If part(index) <> “” Then
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:2'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>tmp = part(index) & tmp
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>End If
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>Next
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-tab-count:1'> style='mso-bidi-font-size:12.0pt'>FnReadString = tmp
style='font-size:10.0pt;font-family:"Courier New";color:blue'> color=blue face="Courier New">
style='font-size:10.0pt;font-family:"Courier New";color:blue'>style='mso-bidi-font-size:12.0pt'>End Functionface="Courier New">
Comparing both methods
style='mso-bidi-font-size:12.0pt'>Consider concatenating the alphabet one
character at the time. Then the following concatenations occur.
style='mso-bidi-font-size:12.0pt'>For simplicity sake the maximum length of
part(0) in the AddString subroutine is set to 1 (color=blue face="Courier New">If Len(part(0) > 1 Then)
Step
|
Concatenations method 1
|
Concatenations method 2
|
1
|
face=Verdana>
|
face=Verdana>
|
2
|
a&b
|
a&b
|
3
|
ab&c
|
face=Verdana>
|
4
|
abc&d
|
c&d, ab&cd
|
5
|
abcd&e
|
face=Verdana>
|
6
|
abcde&f
|
e&f
|
7
|
abcdef&g
|
face=Verdana>
|
8
|
abcdefg&h
|
g&h, ef&gh, abcd&efgh
|
9
|
abcdefgh&I
|
face=Verdana>
|
10
|
abcdefghi&j
|
I&j
|
face=Verdana>
|
etc.
|
face=Verdana>
|
style='mso-bidi-font-size:12.0pt'>I have run tests, series of 10,000 string
concentrations with a length of 100 * (rnd() ^ 3). Using method 1, it took 30
seconds to process the script, while method 2 took less than a second. I used a
Pentium 3, 500 Mhz Processor with 256Mb RAM machine.