Speed test results if interested

[John Bundy]

I thought some may be interested in the results of a speed test I ran to
verify some processing theories.
Machine:
HP
Intel Core 2
T7200 @ 2.00GHz,2.00GHz
2.00GB Ram
Software:
XP sp2
Excel 2003

Scenario:
1 column of 10,000 numbers, 1 column of 10,000 letters, and 1 column of
randomly 10,000 numbers from the first column. The test was for various
lookup methods in VBA. Each was tested 3 times, listed are the middle times.

Test1a: pull each column into 2 seperate arrays, loop through 1 until the
answer is found then stop and go to the next. Time: 6.816 sec.

Test1b: pull each column into 2 seperate arrays, vlookup the data in the
second array and return the result. Time: 85.83 sec.

Test1c: insert a vlookup formula directly into the cell. Time: 6.093 sec.

Test1d: hybrid, put the vlookup in 1 cell with different criteria, then put
result in array. Time:12.94 sec.

Next I wanted to optimize the code for placing the formula directly into the
sheet;
using Application.Screenupdating=False the 6.093 sec was reduced to 5.953
sec. not too significant, but pairing that with
application.calculation=xlManual the time reduced to 2.296 seconds, a vast
improvement.

The second series pitted the best two methods (formula directly in the cell,
loop array against another array) against each other at 65536 cells.

Test2a: looping through one array to find a match method. Time: 301.855
Test2b: formula directly in cell. Time: 73.320

Conclusion: I had predicted from other posts on the matter that formula in
cell would be faster, but i didn't think the difference would be that large.
always turn off screenupdating and automatic calculation (don't forget to
turn it back on!). Any thoughts, or other methods are welcome.

looping method, 10000 results, 0.000681641 sec/result
looping method, 65536 results, 0.004605942 sec/result

direct method, 10000 results, 0.000229688 sec/result
direct method, 10000 results, 0.001118779 sec/result

 

[Bill Renaud]

Can you post the exact code you used for each test?

 

[joeu2004]

Test1a: pull each column into 2 seperate arrays, loop through 1 until the
answer is found then stop and go to the next. Time: 6.816 sec.
[....]
Test1c: insert a vlookup formula directly into the cell. Time: 6.093 sec.
[....]
The second series pitted the best two methods (formula directly in the cell,
loop array against another array) against each other at 65536 cells.

Test2a: looping through one array to find a match method. Time: 301.855
Test2b: formula directly in cell. Time: 73.320

Conclusion: I had predicted from other posts on the matter that formula in
cell would be faster, but i didn't think the difference would be that large.

This might largely be due to the time it takes to copy data between
Excel ranges and VBA arrays. If that's the case, the impact would
depend on the relative cost of the copying to the other processing in
the algorithm. At least, that is what I found in my experiments.

 

[John Bundy]

I THINK this is the code as tested, i might have played with a couple little
things. but those should be obvious because they don't work

Sub testArray()
Dim a1(65536, 2) As Variant
Dim a2(65536, 1) As Variant

Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer



For i = 1 To 65536
a1(i, 1) = Cells(i, 1)
a1(i, 2) = Cells(i, 2)
a2(i, 1) = Cells(i, 3)

Next

For i = 1 To 65536

For j = 1 To 65536

If a2(i, 1) = a1(j, 1) Then
Cells(i, 4) = a1(j, 2)
Exit For
End If
Next
Next

EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"

End Sub

Sub testVlookup()
Dim a1(10000, 2) As Variant
Dim a2(10000, 1) As Variant

Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer



For i = 1 To 10000
a1(i, 1) = Cells(i, 1)
a1(i, 2) = Cells(i, 2)
a2(i, 1) = Cells(i, 3)

Next

For i = 1 To 10000
Cells(i, 4) = Application.VLookup(a2(i, 1), a1, 2, False)
Next

EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"

End Sub



Sub testFunction()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer

For i = 1 To 65536
Cells(i, 4) = "=VLOOKUP(C" & i & ",$A$1:$B$65536,2,FALSE)"
Next
EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub testHybrid()
Dim a1(10000, 2) As Variant
Dim a2(10000, 2) As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer



For i = 1 To 10000
a1(i, 1) = Cells(i, 1)
a1(i, 2) = Cells(i, 2)
a2(i, 1) = Cells(i, 3)

Next

For i = 1 To 10000
Cells(1, 4) = "=VLOOKUP(C" & i & ",$A$1:$B$10000,2,FALSE)"
a2(i, 2) = Cells(1, 4)
Next
For i = 1 To 10000
Cells(i, 4) = a2(i, 2)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"

End Sub