PDA

View Full Version : Sleeper: Function Speed testing..



Zack Barresse
04-11-2005, 03:40 PM
Hi everybody, :006:

Playing around with some functions here. I was wondering if anybody knew which one was faster. I don't have time to test extensively as I have a lot of work to do, and I'm a little lazy. :D




Option Explicit
Public RegEx As Object
Public RegMatchCollection As Object
Public RegMatch As Object

'Function #1

Private Function RemoveAlpha(charName As String) As String
Dim oStr As String, tmpStr As String, wf As WorksheetFunction, fStr As String
Set RegEx = CreateObject("vbscript.regexp")
Set RegMatchCollection = RegEx.Execute(charName)
RegEx.Global = True
RegEx.Pattern = "\D+"
oStr = ""
tmpStr = charName
For Each RegMatch In RegMatchCollection
oStr = RegMatch
tmpStr = Application.WorksheetFunction.Substitute(tmpStr, _
oStr, vbNullString, 1)
Next
RemoveAlpha = tmpStr
Set RegMatchCollection = Nothing
Set RegEx = Nothing
End Function

'Function #2

Private Function RemoveAlpha2(charName As String)
Dim i As Long, tmpStr
For i = 1 To Len(charName) Step 1
If Asc(Mid(charName, i, 1)) > 48 Then
If Asc(Mid(charName, i, 1)) < 57 Then
tmpStr = tmpStr & Mid(charName, i, 1)
End If
End If
Next i
RemoveAlpha2 = tmpStr
End Function

Ken Puls
04-11-2005, 04:39 PM
Okay, like a moron, I bit on this...

Using 65536 rows, I ran a test on one column. (Just 65536 cells) looping through using each one.

The regex one completed in 110 seconds... the other one is still running. It's been going longer than the Energizer bunny, and I think I'm going to have to kill it now! :doh:

Zack Barresse
04-11-2005, 04:49 PM
Thanks Ken!! :yes That is using Late Binding also. If you also get a chance to test using Early Binding, I think the results would be maaahvelus. :D

Reference is: Microsoft VBscript Regular Expressions 5.5

Ken Puls
04-11-2005, 04:50 PM
Hey, just retested...

It makes an incredible difference when you code correctly... :doh:

Zack, here's the routine I used:


Sub testf1()
Dim cl As Range, starttime As Long, endtime As Long
Application.ScreenUpdating = False
starttime = Timer
For Each cl In Range("A1:A10000")
cl.Value = RemoveAlpha("a")
Next cl
endtime = Timer
Application.ScreenUpdating = True
MsgBox "Procedure took " & endtime - starttime & " seconds to run."
End Sub

Sub testf2()
Dim cl As Range, starttime As Long, endtime As Long
Application.ScreenUpdating = False
starttime = Timer
For Each cl In Range("B1:B10000")
cl.Value = RemoveAlpha2("a")
Next cl
endtime = Timer
Application.ScreenUpdating = True
MsgBox "Procedure took " & endtime - starttime & " seconds to run."
End Sub


Filled in random data that look like this: 123456a7890 in cells A1:B10000. Ran the above.

My return times were 17 seconds for the regex one and 6 seconds for the second one.

One caveat though... they seem to do different things. From the name, I was expecting them to remove the a. The regex leaves only the a, the second proc leaves me with a blank cell. Don't know how much impact that will have overall. I figured you could work that out though. ;)

Tommy
04-11-2005, 06:55 PM
I ran a string with a length of 768 characters in an array the size of 500.

RemoveAlpha 0.297 -0.36 sec
RemoveAlpha2 0.25 sec

Same test with 10000 array

RemoveAlpha 6.0789 - 6.0779 sec
RemoveAlpha2 5.157 - 5.134 sec

These are my results and I'm sticking to them :)

The Code used:



Option Explicit
Public RegEx As Object
Public RegMatchCollection As Object
Public RegMatch As Object

' Function #1

Private Function RemoveAlpha(charName As String) As String
Dim oStr As String, tmpStr As String, wf As WorksheetFunction, fStr As String
Set RegEx = CreateObject("vbscript.regexp")
Set RegMatchCollection = RegEx.Execute(charName)
RegEx.Global = True
RegEx.Pattern = "\D+"
oStr = ""
tmpStr = charName
For Each RegMatch In RegMatchCollection
oStr = RegMatch
tmpStr = Application.WorksheetFunction.Substitute(tmpStr, _
oStr, vbNullString, 1)
Next
RemoveAlpha = tmpStr
Set RegMatchCollection = Nothing
Set RegEx = Nothing
End Function


'Function #2

Private Function RemoveAlpha2(charName As String)
Dim I As Long, tmpStr
For I = 1 To Len(charName) Step 1
If Asc(VBA.Mid(charName, I, 1)) > 48 Then
If Asc(VBA.Mid(charName, I, 1)) < 57 Then
tmpStr = tmpStr & VBA.Mid(charName, I, 1)
End If
End If
Next I
RemoveAlpha2 = tmpStr
End Function
Sub Testtt()
Dim StartT As Double
Dim StopT As Double
Dim a As String
Dim I As Integer
Dim aa As String
Dim ab(1 To 10000) As String
For I = 1 To 10000
ab(I) = "CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO.CD - NATIONAL BANK AND TRUST CO."
Next
StartT = Timer
For I = 1 To 10000
a = RemoveAlpha2(ab(I))
Next
StopT = Timer
MsgBox StopT - StartT
Debug.Print StopT - StartT
StartT = Timer
For I = 1 To 10000
a = RemoveAlpha(ab(I))
Next
StopT = Timer
MsgBox StopT - StartT
Debug.Print StopT - StartT
End Sub

Zack Barresse
04-12-2005, 08:09 AM
Thanks guys!

Ken: The test line should be ...

cl.Value = RemoveAlpha2(cl.Value)
.. as it needs to test the entire string. Interesting results though.

Tommy: Interesting results indeed. When I get time (next couple of weeks) I'd like to test with Early Binding as opposed to Late Binding as I don't think I'm going to distribute this function, it's just for me. :evillol: