VBA speed i7 vs i3

[eos0001]

Hello!

Possible that when i run a following VBA script on i7-6700k the result is 70 sec and when i run on i3 - 8100 the result is 32 sec?

Sub TimerProbe()

Workbooks("probe speed.xlsm").Activate


Dim start As Double, fine As Double, tempo As Double
start = Timer
Sheets("Foglio2").Range("G1").Value = 1
Application.ScreenUpdating = False
Dim i As Integer, d As Long

For i = 2 To 60
For d = 1 To 5000
Cells(d, 5 + i).Value = d

Next d
Next i
Application.ScreenUpdating = True



fine = Timer
tempo = fine - start
test = "Tempo " & tempo & " sec."
Debug.Print test
End Sub

Have any solution speed up the VBA on core i7?
I guess this CPU must be faster than i3

Thank you for the answer

[KOKOSEK]

On my laptop: Intel(R) Core(TM) i5-7200U CPU @ 2.50GHz - Tempo 10.21875 sec.
I am not sure that only processor is responsible about this.

[rorya]

I'd suggest you populate an array and write that to the range in one go, which should be a lot faster on both processors. Writing cell-by-cell is the worst option if it's avoidable.

[curiouscat408]

Possible that when i run a following VBA script on i7-6700k the result is 70 sec and when i run on i3 - 8100 the result is 32 sec?

The obvious answer is "yes", since that it is what you measured.

-----

Have any solution speed up the VBA on core i7?

The performance is dominated by Excel recalculation time and O/S overhead (context switching between VBA and Excel).

Are the Excel files identical in both cases?

The difference might also be due to extraneous system factors, for example internet interrupts and other active applications.

Unless you are intentionally measuring extraneous factors, I would make the following changes, at a minimum.

Please Login or Register  to view this content.

The loop execution is still dominated by extraneous factors. Very little of the measured time is due to VBA execution.

But setting Manual calcuation might improve the loop performance, depending on what else is going on in the Excel file.

If you want to include the (one) Excel recalculation and screen update overhead in the performance measure, as you were doing, move the statement ``fine = Timer`` to after the second With block.

[Marc L]

Hello,

on an old tired tests laptop i3-2328M @ 2.20 GHz under Win 8.1 & Excel 2010 32 bits : 10.8125 sec …

[Marc L]

On the same setup this VBA demonstration needs less than a second (0.938) :

Sub Demo1() 
  Const C = 59, R = 5000 
    Dim T!, L%, K%, N%(1 To R, 1 To C) 
        [G1].CurrentRegion.Clear 
        T = Timer 
    For L = 1 To R 
    For K = 1 To C 
        N(L, K) = L 
    Next K, L 
        [G1].Resize(R, C) = N 
        Debug.Print "Demo1 : "; Format(Timer - T, "0.000 sec") 
End Sub  

[Marc L]

On the same setup this VBA demonstration needs less than 0.3 sec :

Sub Demo2() 
        Dim T!, R& 
    With [G1:BM5000].Rows 
       .Clear 
        T = Timer 
        Application.ScreenUpdating = False 
        For R = 1 To .Count:  .Item(R) = R:  Next 
    End With 
        Application.ScreenUpdating = True 
        Debug.Print "Demo2 : "; Format(Timer - T, "0.000 sec") 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[curiouscat408]

I got the following execution times on my laptop with an i7-6820HQ @ 2.70 GHz.

1. About 2.1 sec for the original loop

Please Login or Register  to view this content.

2. About 0.14 sec for an array implementation (rorya's suggestion)

Please Login or Register  to view this content.

3. 0.08 sec for an optimized version of the original loop

Please Login or Register  to view this content.

I am surprised by the improvement of #3 over #2.

BTW, I think the huge difference in execution times for the original loop (#1) demonstrates my original point: for eos0001, the execution time is dominated by extraneous factors.

[Marc L]

I am surprised by the improvement of #3 over #2.

Same result as mine with my both demonstrations above and I can't be surprised as it's well known since last century …

[rorya]

I am surprised by the improvement of #3 over #2.

Writing a simple data value to a multi-cell range is much faster than writing an array of values to the same range. I admit I hadn't realised quite how much faster until you posted those timings. It seems that more than compensates for the looping of the rows.

[curiouscat408]

Writing a simple data value to a multi-cell range is much faster than writing an array of values to the same range.

Beware of absolutes when speaking of performance -- especially Excel/VBA performance. I notice a lot of variability.

In general, it depends on the relative overhead costs. And YMMV depending on the CPU and cache size, Excel spreadsheet complexity, and extraneous environmental factors.

Refer to the code at the end.


1. For a range of 5000 rows by 10 columns:

array test: 0.023251 sec +/- 0.68%
range test: 0.063771 sec +/- 0.23%

The multiple range assignments take nearly 2.75 times longer than the single-range array assignment.

(On my computer and in my system environment. YMMV.)


2. For a range of 5000 rows by 59 columns (eos001's example):

array test: 0.136233 sec +/- 0.34%
range test: 0.090081 sec +/- 0.28%

The single-range array assignment took more than 1.5 times longer than the multiple range assignments.

(On my computer and in my system environment. YMMV.)


The times are an average of 20 trial runs +/- the 95% confidence interval (as a percentage of the average).

The multiple range assignments are subject to a lot of system overhead involved in the context switch and communication between VBA and Excel.

OTOH, the single-range array assignment is subject to the overhead of transferring larger amounts of data between VBA and Excel, which is more CPU-intensive and sensitive to CPU cache size.


-----

Please Login or Register  to view this content.

I use QueryPerformanceCounter because the times for some of my tests are significantly less than 0.015625 sec, the typical system clock resolution on Windows systems.

If your computer does not support QueryPerformanceCounter, change:

QueryPerformanceCounter sc
[....]
QueryPerformanceCounter ec
t(i) = convertMyTimer(ec - sc)

to:

t(i) = Timer
[....]
t(i) = Timer - t(i)

But beware of potentially large timing errors when measuring short intervals, which might adversely affect conclusions.

[Artik]

Yet another way:

Please Login or Register  to view this content.

Artik

[Marc L]

Yes Artik, according to TEBV rule your procedure is the fastest on my side : 0.172 sec, well done !

[rorya]

Beware of absolutes when speaking of performance -- especially Excel/VBA performance. I notice a lot of variability.

Very true.

I do notice in some quick tests this morning, that although the number of rows doesn't appear to make a big difference to the relative speeds of each approach (obviously both increase with more rows), the number of columns does seem to. Using 25 columns, whether 5,000 rows or 50,000, the two approaches take very similar times. Using 50 columns the range approach is consistently faster and the more columns, the wider the gap between the two. Do you see the same?

I'm using 2016 FYI.