VBA Code Performance

[rhythmscribe]

Hi,

I've written two versions of the same code, the purpose of which is to associate records from one table to the appropriate sales rep based upon the record's postal code. Version 2 is 3x slower than version 1, and I have isolated the performance difference between the two versions to the following lines (i.e. all other code is the same in both versions):

Version 1:

*FindCSR is the function name that returns the name of a sales rep if a match is made
*blnCheck is a Boolean variable
*postalcode is a parameter passed to this procedure containing the record's postal code value
*ZipRange is a postal code range (i.e. 925*) taken from the sales rep table

blnCheck = postalcode Like ZipRange
If blnCheck = True Then
FindCSR = rstCSR.Fields(FLD_CSR_ID)
GoTo FindCSR_Exit
End If

Version 2:

*FindCSR is the function name that returns the name of a sales rep if a match is made
*postalcode is a parameter passed to this procedure containing the record's postal code value
*postalcode_low is a value grabbed from the sales rep table that corresponds to the low end of a postal code range (i.e. 92500)
*postalcode_high is a value grabbed from the sales rep table that corresponds to the high end of a postal code range (i.e. 92599)

If postalcode >= postalcode_low _
And postalcode <= postalcode_high Then
FindCSR = rstCSR.Fields(FLD_CSR_ID)
GoTo FindCSR_Exit
End If

Why would Version 2 take 3 times as long as Version 1? Is there anything I can do to improve the performance?

Any advice is appreciated. If I need to provide more info, let me know.

Thanks,

-Mike

[DSJ]

I have no definite answer but a few ideas/comments. First, I would think the Like statement would only have to compare the first 3 digits to return an anwer, while the second would have to compare all 6 twice (all 6 for the low value and all 6 for the high value)... Also, make sure postalcode, postalcode_low and postalcode_high are all the same data type (ie. integers or strings) so that VB doesn't have to convert them back and forth (ie. "12345" = 12345 is true but should take longer to compare than 12345 = 12345 or "12345" = "12345").

[DSJ]

After doing a little testing with the following code, my quess is you probably have a situation like under my command4 button, which is definitely the worst way to go of the four...


option Explicit
private Declare Function GetTickCount Lib "kernel32" () as Long

private Sub Command1_Click()
Dim x as string
Dim i as Long
me.print GetTickCount
x = "98743"
for i = 1 to 100000
If x Like "987*" then
'MsgBox "yes"
End If
next i
me.print GetTickCount
End Sub

private Sub Command2_Click()
Dim x as Long
Dim i as Long

me.print GetTickCount
x = 98743
for i = 1 to 100000
If x >= 98700 And x <= 98799 then
'MsgBox "yes"
End If
next i
me.print GetTickCount
End Sub

private Sub Command3_Click()
Dim x as string
Dim i as Long

me.print GetTickCount
x = "98743"
for i = 1 to 100000
If x >= "98700" And x <= "98799" then
'MsgBox "yes"
End If
next i
me.print GetTickCount

End Sub
private Sub Command4_Click()
Dim x as string
Dim i as Long

me.print GetTickCount
x = "98743"
for i = 1 to 100000
If x >= 98700 And x <= 98799 then
'MsgBox "yes"
End If
next i
me.print GetTickCount

End Sub

[rhythmscribe]

Actually, it's more like your command3 button, since I'm comparing string to string. The reason for that is that the postal codes contain both US and Canada values, and the Canada postal codes are not numeric.

Are numeric comparisons faster than string comparisons? Does the code need to convert the strings to a numeric representation prior to the comparison?

-Mike