Speed test results if interested

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Bundy - 07 Dec 2007 16:57 GMT

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

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

Reply to this Message

Bill Renaud - 08 Dec 2007 05:03 GMT

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

Signature

Regards,
Bill Renaud

Reply to this Message

John Bundy - 10 Dec 2007 16:13 GMT

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

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

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

Reply to this Message

joeu2004 - 09 Dec 2007 01:52 GMT

> 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.
[quoted text clipped - 9 lines]
> 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.

Reply to this Message