Swiftness of code and evaluation of strings

[Banaticus]

Which is faster? Different parts are embedded in a Select Case or an If loop so I can't say "theString = "beginning middle ending".

Dim stringBeginning as String
Dim stringMiddle as String
Dim stringEnding as String
stringBeginning = "beginning part"
stringMiddle = "middle part"
stringEnd = "end part"
RowSource = stringBeginning & stringMiddle & stringEnd

Dim theString as String
theString = "beginning part"
theString = theString & "middle part"
theString = theString & "end part"
RowSource = theString

 

[Juan]

Interesting question - but I don't know the answer. I saw a similar question quite some time ago in an Excel forum. I think that the solution was to time the possibilities. Some timer code (Start and then Stop) was put around the code to be tested and, as the code would execute very quickly, it was looped maybe 100 or 500 times between Start and Stop to get a measurable difference which was displayed via a message box.

I expect one of the experts here will know instinctively which is faster, but I just wonder if this approach might be of help.

 

[Banaticus]

I set up all variables before the loop, then For i = 1 To 83646 ... Next i
I checked for time before and after the loop. The time difference varied from just over 5 seconds to just over 6 seconds, depending on what else was running in the background (a few processes that I can't shut off). Each loop hit the same time several times.

I thought that:
stringBeginning = "beginning part"
stringMiddle = "middle part"
stringEnd = "end part"
RowSource = stringBeginning & stringMiddle & stringEnd

would be faster, as in some languages:
theString = theString & "middle part"
Accesses theString twice, once to add it to "middle part", then again to save it as theString.

So, I don't think it matters at all which one is used.

Private Sub TesterButton_Click()
    Dim TesterFalse As Boolean
        TesterFalse = False
    Dim SelectCaseThing As String
        SelectCaseThing = "All"
    Dim SqlString As String
    Dim i As Long
    Dim StartTime As Date
    Dim EndTime As Date
'Get Ready, Get Set, Go!
StartTime = Time
For i = 1 To 83646
    SqlString = "SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name FROM [App Info] WHERE"
    Select Case SelectCaseThing
        Case "All"
            SqlString = SqlString & " ([App Info].[Last Name] <> ' ')"
    End Select
    If TesterFalse = False Then
        SqlString = SqlString & " AND [App Info].[App Type] <> 'Canceled/Terminated'"
    Else
    End If
    SqlString = SqlString & " ORDER BY REPLACE(Nz([App Info].[Last Name]),' ',''), REPLACE(Nz([App Info].[First Name]),' ',''), REPLACE(Nz([App Info].MA),' ','');"
    Me.Names_Combo_Box.RowSource = SqlString
Next i
EndTime = Time
MsgBox (StartTime & ", " & EndTime & ", " & EndTime - StartTime)
End Sub

Private Sub TesterButtonJunior_Click()
    Dim TesterFalse As Boolean
        TesterFalse = False
    Dim SelectCaseThing As String
        SelectCaseThing = "All"
    Dim BeginningString As String
    Dim MiddleOneString As String
    Dim MiddleTwoString As String
    Dim EndString As String
    Dim i As Long
    Dim StartTime As Date
    Dim EndTime As Date
'Get Ready, Get Set, Go!
StartTime = Time
For i = 1 To 83646
    BeginningString = "SELECT [App Info].[Soc Sec #] as [Soc Sec], [Last Name] & ', ' & [First Name] & ' ' & [MA] AS Name FROM [App Info] WHERE"
    Select Case SelectCaseThing
        Case "All"
            MiddleOneString = " ([App Info].[Last Name] <> ' ')"
    End Select
    If TesterFalse = False Then
        MiddleTwoString = " AND [App Info].[App Type] <> 'Canceled/Terminated'"
    Else
    End If
    EndString = " ORDER BY REPLACE(Nz([App Info].[Last Name]),' ',''), REPLACE(Nz([App Info].[First Name]),' ',''), REPLACE(Nz([App Info].MA),' ','');"
    Me.Names_Combo_Box.RowSource = BeginningString & MiddleOneString & MiddleTwoString & EndString
Next i
EndTime = Time
MsgBox (StartTime & ", " & EndTime & ", " & EndTime - StartTime)
End Sub

It occurs to me ...

Dim stringBeginning as String
Dim stringMiddle as String
Dim stringEnding as String
stringBeginning = "beginning part" <--StringBeginning is accessed once
stringMiddle = "middle part" <--StringMiddle is accessed once
stringEnd = "end part" <--StringEnd is accessed once
RowSource = stringBeginning & stringMiddle & stringEnd <--each is accessed once more
Grand total for each string part: Accessed Twice, for 6 accesses

Dim theString as String
theString = "beginning part" <--Accessed once
theString = theString & "middle part" <--Accessed twice = thrice & ""
theString = theString & "end part" <--Accessed fourth = fifth & ""
RowSource = theStringvv<--Accessed sixth
The string part is accessed 6 times

Essentially, it's that old adage, 6 [in] one [hand], half a dozen [in] the other.

 

[Juan]

I guess that the example that you gave initially is relatively simple and the time differences are likely to be very small. I acknowledge the timer itself may be affected by other processes.

I know that, as a relative newbie, I'm happy to create some code that works with a few data examples but it's when the database grows to many thousand items that need manipulation that the knowledge of the experts in the field earn their cash. They know exactly which of the various techniques is best suited to the situation.