Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed test results if interested
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 Softwa 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 -- -John Please rate when your question is answered to help us and others know what is helpful. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed test results if interested
Can you post the exact code you used for each test?
-- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed test results if interested
On Dec 7, 8:57 am, John Bundy (remove) wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed test results if interested
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "Bill Renaud" wrote: Can you post the exact code you used for each test? -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can colors be used in if test results eg red for minus amounts | Excel Worksheet Functions | |||
I SEE NO VB CODE AND YET IT HIGHLIGHTS SAT N SUNDAYS. ANYONE INTERESTED TO FIND OUT HOW IT IS DONE. | Excel Discussion (Misc queries) | |||
Interpretation of T test results | Excel Worksheet Functions | |||
Test Results in Excel | Excel Programming | |||
i am interested in buying PDA that support micosoft excel macros | Excel Programming |