Excel 2016 perf what the effing eff?

Well, I tried to resist, but shocked at the rank day to day performance of my shiny Win10E2016 toy I had to compare performance.

Obviously I haven’t done it properly, what would be the point in that?

But equally obviously I’m going to write about it as if it is indisputable fact.

I wanted a test that mimicked the slow UI response I was seeing so I selected the cells. I am in the throes of writing a more rounded benchmarking tool, but this was my first area of focus. Of course it ignores MTA etc etc, those tests are coming.

I’ll stick the code at the bottom. I would be delighted if you copied it into a new workbook module and ran it on your machine(s) and replied with the results and a hint about machine specs and Excel versions. Preferably in a clean Excel, not one shared with a workbook polluted with a quadzillion volatile VBA UDFs, or a million addins trapping the selectionChange event!

4 core i7 w 16GB RAM – W10E2016 – average of ten results 2.4 seconds

dual core i7 VM (with 500GB ram?(it thinks!)??) WXPE2010 av 0.14 secs

dual core i7 VM (with 500GB ram???) WXPE2003 av 0.12 secs

I ran it on a single screen in all cases, with just the VBA IDE visible. If the Excel grid is visible the performance is much worse.

I expected 2016 to be slower in this single threaded test, but not 10 times slower. What are they doing with all those cycles? (cryptomining???)

cheers

simon

Here is that code, please please take a minute and let me know your results

(if you are using a lapper be sure it does not have some work dodging power/performance reducing profile set)

Public Sub multiLoop()
Dim x As Integer
Dim t As Single

For x = 1 To 10
t = Timer

CellEntryTest 3.1

Debug.Print Timer – t
Next x
End Sub

Public Sub CellEntryTest(seed As Double)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

ws.Activate

ws.Cells.Clear

Dim x As Long

For x = 1 To 1000
ws.Cells(x, 1).Select
ActiveCell.Value = (seed + x) * 2 * Rnd(2)
Next x

End Sub

Feel free to chip in if you are convinced this is an invalid test.

If you are seeing sub 1 second response you could bump up the loop to 10k, that is what I had it on until I came to test 2016! (mention that in you reply, or just divide by 10)

 

Advertisements