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

16 Responses to “Excel 2016 perf what the effing eff?”

  1. Hugh Watkins Says:

    I ran the code three times and averaged around 85 seconds per run. But it is a hot, Friday afternoon.

    I work in Romania on Office 365 but my server is in Poland. As far as i can see from the Control Panel, this is what I run on:

    Windows Server R2 Datacenter
    Processor: Intel Xeon E5-2698v4@2.20GHz
    Memory: 40.0GB
    System type: 64-bit OS; x64 processor

  2. Simon Says:

    Thanks Hugh
    so about 8 secs per cycle?

    here are mine (from the immediate window – I didnt mention that) (0.1 and 0.18)

    Win XP Excel 2003
    0.077417
    0.095365
    0.112449
    0.10378
    0.099819
    0.103401
    0.102855
    0.101062
    0.107281
    0.106809

    Win XP Excel 2010
    0.105327
    0.172248
    0.172869
    0.224259
    0.193983
    0.188399
    0.181604
    0.176193
    0.178742
    0.176452

  3. dougaj4 Says:

    Hi Simon – I only noticed you had returned to regular updates here quite recently, so a belated welcome back.

    I’ve noticed complaints about the (lack of) speed of Excel 2016 before (at Daily DoE for instance), but I hadn’t actually noticed it myself, so I thought I’d give your benchmark a go.

    I’m using Excel 365 (updated whenever they feel like it) on a laptop with Windows 10andi7 4 Core@2.6GHz (8 logical cores, whatever that means).

    Times with your code were (all times / 1000 loops):

    Two files – multiloop opened 2nd 14.4
    Two files Loops reduced to 100 8.33
    Multiloop only; 1000 Loops 2.50
    Two files – multiloop opened first 15.2
    Two files – multiloop opened first; recalc manual 2.15
    Multiloop only; 1000 Loops recalc manual 2.05

    So same as you, or slower.

    I thought I’d check performance if the loops wrote to an array, then clear cells and write to the spreadsheet in a single operation (code pasted below). I increased the loops to 1 million after a few trials, but times below are per 1000:

    Write array, Multiloop only, 1000000 loops 0.00108
    Write array, Two files, 1000000 loops 0.00107
    Write array, Two files, 1000000 loops; recalc auto 0.00112

    So that way is about 2000 x faster, or about 10,000 x faster if you have two files open.

    That means it’s quicker to generate an array with 1000 rows and write it to the spreadsheet than it is to select a new cell and write a single value to it.

    I expected a big difference, but not that much, but I have checked the array method does actually write to all million rows for each outer loop.

    Here’s the code:
    Public Sub CellEntryTest2(seed As Double)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)
    Dim Loops As Long, NumA() As Double, RtnRange As String
    Dim x As Long
    Loops = 1000000
    ReDim NumA(1 To Loops, 1 To 1)

    For x = 1 To Loops
    NumA(x, 1) = (seed + x) * 2 * Rnd(2)
    Next x

    ws.Activate

    ws.Cells.Clear
    RtnRange = “A1:A” & Loops
    ws.Range(RtnRange).Value2 = NumA
    End Sub

  4. Simon Says:

    Thanks Doug the array v single cell selection comparison is insane!
    I need to finish my other tests…
    (8 logical cores mean 4 real cores and 4 fake hyperthread pretend ones. mines the same :-))

  5. SmileyFtW Says:

    I had 1000 rows of cells… you really want that? W7 64 bit OS; Excel 2010.

  6. Simon Says:

    Smiley no, not the Excel data, the output from the immediate window in VBA. should be a list of 10 numbers. thanks

  7. Brian Canes Says:

    55706.55 55704.34
    55708.78 55706.55
    55711.02 55708.78
    55713.13 55711.02
    55715.21 55713.13
    55717.32 55715.21
    55719.45 55717.32
    55721.5 55719.45
    55723.54 55721.5
    55725.58 55723.54

    NUMBER_OF_PROCESSORS=4
    OS=Windows_NT
    PROCESSOR_ARCHITECTURE=x86
    PROCESSOR_ARCHITEW6432=AMD64
    PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 69 Stepping 1, GenuineIntel
    PROCESSOR_LEVEL=6
    PROCESSOR_REVISION=45
    Surface 2
    Windows 10
    MSO 2013

    Regards
    Brian Canes

  8. roryarchibald2013 Says:

    Excel for Mac 2016, build 16.17 (180816). macOS High Sierra. 2.3GHz i5, 16GB RAM:

    1.792969
    1.867188
    1.796875
    1.800781
    1.796875
    1.8125
    1.800781
    1.792969
    1.800781
    1.796875

  9. Simon Says:

    Thanks Brian and Rory.
    Brian what do those times mean?55k seconds is nearly a full day??

    Charlie (still blocked from wordpress seemingly) sent these:

    4 core i7-8550 w 16GB – W10E2010 – avg 0.7

    4 core i7-8550 w 16GB – W10E2016 – avg 1.6

    4 core i7-4810 w 24GB – W07E2010 – avg 0.6

    4 core i7-4810 w 24GB – W07E2016 – avg 1.8

    And he wondered about my 0.16 – that was on Win XP which is clearly faster than Win7 or newer. 0.5 or 0.6 is the best I have seen on Win 7.

    Interesting that 0.1 of the slowdown looks to be Win7-10 and 1.0 of it Excel 2010-E2016. That’s what we are seeing too, Win10 is only slightly slower but Excel 2016 is a mess. I wonder if its a debug build???

    • roryarchibald2013 Says:

      Your code came through for me on Excel-L as:

      Debug.Print Timer; –; t

      which I amended but I suspect Brian didn’t.

      FWIW the Mac timings were a lot better than I would have expected. Obviously the move to common code base is good for Mac and terrible for Windows users. ;)

  10. Simon Says:

    Here are some more figures folks provided:

    https://smurfonspreadsheets.wordpress.com/2018/08/22/more-perf-figures/

  11. dougaj4 Says:

    Rory – I had the same problem with the Debug.Print Timer line. Brian’s results make sense if you subtract the two values.

    Simon – It’s clear that Excel 2016 is much slower for the operation being benchmarked here, but doesn’t that just mean that code that was really slow before is now really really slow?

    I have just posted some examples using Evaluate on arrays which gives reasonable performance from Excel 2016. If anyone can run it in Excel 2010,that would be interesting.

    https://newtonexcelbach.com/2018/08/24/using-vba-evaluate-as-an-array-function/

  12. dougaj4 Says:

    This might be of interest too; Charles Williams on Excel 365 performance improvements:

    https://fastexcel.wordpress.com/2018/01/29/excel-2016-performance-improvements/

  13. Simon Says:

    Doug, I developed this benchmark specifically to test a reported user experience issue around screen updating. Ie its so bad users can see it when working.

    So yes, this slow code now runs (much much) slower, but that is not all, its very visible to power users just clicking around.

    In fact fast code now runs slower too, I’ve read Charles’ post, but I am struggling to experience any of these improvements. (VBA big array handling might be better, need more tests)

  14. dougaj4 Says:

    Simon, OK, but in my opinion Excel 365/2016 is not as bad as the benchmarks make it look.

    Clearly some people disagree with that opinion:)

    p.s. Did you know that a Bing search on smurfonspreadsheets does not find a single direct link to smurfonspreadsheets? Whether that is a Microsoft conspiracy to deal with people who don’t like the latest Excel, or maybe a WordPress thing, I don’t know.

    Google works OK though.

  15. Simon Says:

    Doug, you are right its not that bad for most users. And actually good for those using the new features. for some though it is close to unuseable.

    interesting on bing, and me being all positive too :-)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


%d bloggers like this: