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)
Friday, 17th August, 2018 at 11:55 am |
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
Friday, 17th August, 2018 at 5:54 pm |
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
Monday, 20th August, 2018 at 12:41 am |
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
Monday, 20th August, 2018 at 10:36 pm |
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 :-))
Tuesday, 21st August, 2018 at 6:02 pm |
I had 1000 rows of cells… you really want that? W7 64 bit OS; Excel 2010.
Tuesday, 21st August, 2018 at 6:53 pm |
Smiley no, not the Excel data, the output from the immediate window in VBA. should be a list of 10 numbers. thanks
Tuesday, 21st August, 2018 at 8:59 pm |
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
Wednesday, 22nd August, 2018 at 11:59 am |
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
Wednesday, 22nd August, 2018 at 6:11 pm |
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???
Thursday, 23rd August, 2018 at 3:17 pm |
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. ;)
Wednesday, 22nd August, 2018 at 6:56 pm |
Here are some more figures folks provided:
https://smurfonspreadsheets.wordpress.com/2018/08/22/more-perf-figures/
Saturday, 25th August, 2018 at 12:46 am |
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/
Saturday, 25th August, 2018 at 12:58 am |
This might be of interest too; Charles Williams on Excel 365 performance improvements:
https://fastexcel.wordpress.com/2018/01/29/excel-2016-performance-improvements/
Sunday, 26th August, 2018 at 9:45 pm |
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)
Tuesday, 28th August, 2018 at 11:04 pm |
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.
Friday, 31st August, 2018 at 4:29 pm |
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 :-)