none
Massive Performance Decline Since Excel 2003 - Any Suggestions?

    Question

  • I am writing to see if anyone has a suggestion to make my current version of Excel perform calculations and VBA as quickly as older versions of Excel.  I have just installed Office 2016 x64 on a brand-new Windows 10 x64 i7-7500U laptop with 16GB of RAM.  The first thing I did was to fire up an old VBA-enabled workbook that I have been running since 2004 to test Excel's speed.  Unfortunately, the results of the test were worse than the results on my previous machine (which was a 4-year-old processor, less RAM and Excel 2013 x32), which was itself worse than previous machines.  Performance peaked with Excel 2007 on a Dell OptiPlex GX745 with 2GB RAM.  Why does it keep dropping? 

    As background, the Excel workbook and macro were initially set up to solve a highschool math problem.  The workbook fills in random numbers and checks to see if it has happened upon the solution, and if not fills in a new set of random numbers.  I then added some code to have it loop through 1000 iterations and record how many loops it took to solve the problem 1000 times, and divide by the number of seconds it took to run, to give me the number of loops per second.  I know that my code could probably be streamlined significantly (turn of screen updating, etc.), but that’s not the point.  The point is that the same code has been run on 20 different PCs over 13 years, so I should have an apples-to-apples comparison.  Since the PCs have been getting faster, with more/faster RAM, results should be improving.  But instead, they are declining.

    The first machine I ran the test on was an IBM ThinkPad T21 laptop with a P3-800 processor and a whopping 512MB of RAM, which eked out 1,343 loops/second.  The best was the OptiPlex, which did 7,221 loops per second.  My 3-year old Dell Precision T1700 with an i7-4770 and 32GB of RAM (running Office 2016 32-bit) did 3,467 loops/second, half the speed of the ten-year-old OptiPlex!  And my brand-new i7 laptop running Excel 2016 x64 just managed 846 loops/second.  Yes, 50% worse than a 15-year-old ThinkPad, and 88% worse than a 9-year-old desktop.  That is pathetic.

    Is there anything that can be done about this?  It is crazy that Excel’s performance has degraded that significantly over time.  I feel like I must be missing something.

    If it’s helpful, below is the VBA code used.  As you can see, it is nothing fancy.

     
    Sub Loop1()
    
    ' This loop runs until selected cell is True
    
    Dim i As Integer
    Dim Count As Integer
    Dim Timestart As Double
    Dim Timeend As Double
    Dim Timeelapsed As Integer
    
    Range("Loops").ClearContents
    Range("Elapsed").ClearContents
    Timestart = Now
    Timeelapsed = 0
    
        For Count = 42 To 1041
        Range("Current").Value = Count - 41
            i = 0
            Do
            Calculate
            i = i + 1
            Loop Until Range("Overall") = "True"
        Cells(Count, 3).Value = i
        Next Count
        
    Timeend = Now
    Timeelapsed = (Timeend - Timestart) * 86400
    Range("Elapsed").Value = Timeelapsed
    MsgBox ("Nerd Alert: Done!")
    
    End Sub

    Thursday, November 24, 2016 10:12 PM

All replies

  • Gurs,

    Try putting the following lines before the For loop.

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual    'To turn off the automatic calculation

    Then put these after Next Count to see if this improves performance.

        Application.Calculation = xlCalculationAutomatic    'To turn On the automatic calculation
        Application.ScreenUpdating = True

    Regards,

    Harry

    Saturday, November 26, 2016 1:37 AM
  • Thanks for the suggestion Harry. There are a couple of issues though. One issue is that I can't turn off calculation, since the code works by filling in a set of random numbers and having Excel calf the result to see if the conditions are passed. But more fundamentally, my goal is not to speed up this code. In fact, that would defeat the entire purpose of having run the same code over 15 years and recorded the results to assess performance. The point is to have an apples-to-apples comparison of Excel/PC performance across time. If I speed up the code now, I will no longer have an accurate comparison. I knew that turning off screen updating would have made the code faster back then too, but that wasn't the point. My question is why Excel's performance has degraded so precipitously, and what (if anything) can be done about it. Any ideas on that topic?
    • Edited by gurs Saturday, November 26, 2016 2:48 PM Formatting
    Saturday, November 26, 2016 2:47 PM
  • Gurs,

      I have to assume Microsoft changed something in the calculation engine.  I noticed a little performance issues when I went to 2013 from 2007.  It may have been when they changed it from how Excel handles multiple workbooks.

    When I look at the code and MSDN on VBA Calculate, you are asking the code to calculate the whole workbook on each loop.  Maybe you can change it to only calculate the current row or current sheet to see if that improves performance.

    https://msdn.microsoft.com/en-us/library/office/ff834658.aspx

    Regards,

    Harry

    Saturday, November 26, 2016 4:21 PM
  • Those are good suggestions Harry, but my goal is NOT to speed up the code.  My goal is to understand why a 15-year-old laptop with a P3 processor and 512MB of RAM runs the code almost 60% faster than a brand-new state-of-the-art PC with a fast i7 processor and 8GB of RAM, and see if there is anything that can be done to speed up the current build of Excel.
    Saturday, November 26, 2016 6:04 PM
  • gurs,
    Re:  code performance over the years

    MS adds / subtracts features from / to Excel every release.
    Your expectation is that Excel should get better with every release; my hope is that they don't screw it up too badly.
    Several years ago, one of the xl book authors, commented that MS puts a new team on XL every two years.
    This year (xl2016), the XL forums are rife with, "its broke", it doesn't work" complaints.  Changes sell software and that is the aim.

    In your case, if the named ranges encompass entire columns then that might explain the slowdown ( ~1,000,000 cells vs. ~65000).

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Saturday, November 26, 2016 7:50 PM
  • Interesting idea about the size of the column slowing things down Jim!  I checked, but all my named ranges refer to either a single cell or a small range of cells.  I hear your point about MSFT being more focused on adding features than improving performance, but this performance decline is over the top.  I keep hoping someone from Microsoft will jump in here with a reasonable explanation, but I'm not holding my breath...
    Saturday, November 26, 2016 8:27 PM
  • Some things have got slower and some things have got faster. I rather suspect your benchmark hits a number of the slower things whilst ignoring the faster things!

    Some of the faster things are:

    • multi-threaded recalculation exploiting multiple cores (probably does not help your benchmark depending how many formulas you have)
    • read/write blocks of data from Excel to VBA (does not help your benchmark because you are doing things cell-by-cell)

    Some of the slower things are:

    • RAND function - different, improved algorithm but slower (since RAND gives different answers your benchmark may be no longer valid?)
    • Overhead to read and write a single cell from Excel to VBA (your benchmark)
    • Screen updating the rendering layer (more complex conditional formats, screen animation etc) (your benchmark)
    • converging the different Excel code bases into more-or-less a single code base so that Excel can work on multiple platforms/endpoints has (IMHO) had some performance tradeoffs

    For my take on VBA data transfer between Excel and VBA see my blog post here


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Sunday, November 27, 2016 7:11 PM
  • Thanks Charles.  Very interesting post, and I read your blog post too.  My code doesn't do any significant block reading/writing, as you noted, so I doubt that makes much of a difference.  I didn’t disable screen updating, so if that has gotten slower it could certainly have an impact for sure.  To test that impact, I disabled screen updating and re-ran the macro.  Performance improved, but only by about 20%.Presumably that would have helped in 2003 too, so the net impact isn't much.

    So that leaves one likely culprit.  The worksheet that does the heavy lifting doesn't use the RAND function, but there are 5 cells that use RANDBETWEEN, which I'm guessing is similarly slower.  But would 5 uses of that function cause such a massive decline in performance? 

    Just to see what would happen, I deleted 4 random tabs in the workbook (none of which also contained the RANDBETWEEN function) so that the active tab was the only one left.  Since my code recalculates the entire workbook, not the active sheet, I wanted to see what would happen.  Sure enough, performance improved dramatically, by about 300% (even with screen updating turned on).  The same 5 uses of RANDBETWEEN remained.  Does that make any sense to you?

    I uploaded a copy of the file if you are interest, which you can find here.  If you want to have it run only 10 rounds instead of 1000, just change the line in the code from “For Count = 42 To 1401” to “For Count = 42 To 51”.

    Monday, November 28, 2016 5:49 PM
  • I get a massive difference in speed caused by screen updating: about 98% of the time is being used by screen updating.

    So if you do not turn off screen updating speed will vary dramatically depending on screen size etc (basically how many rows and columns are visible and which ones they are!).

    Try switching off screen updating at the start and turning it on again immediately before the Msgbox.

    But as I said before there are a lot of swings and roundabouts performance changes between versions, and so the perf gains/losses can be very dependent on the design of your benchmark.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Tuesday, November 29, 2016 5:52 PM
  • Hi, 

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, December 02, 2016 2:58 AM
    Moderator
  • The responses were interesting, but the question has not really been answered.  I think only MSFT can answer it.  Processors, memory and SSD drives yield PCs that are massively faster than those of 15 years ago.  If we use Moore's Law as a guide and assume overall performance has doubled every 18-24 months, that implies that my PC today is between 128 and 1024 times faster than my PC from 15 years ago.  Yet Excel's calculation performance on my test worksheet has dropped by 33% over the same time period.  After adjusting for the faster hardware, that means Excel 2003 calculated my worksheet between 400 to 3000 times faster than Excel 2016.  MSFT's silence on this topic only confirms that conclusion. 

    Think about that multiple.  The performance decline is nothing short of astounding.  It's really quite sad.  Imagine if cars used to get 100 miles per gallon, but now took 40 gallons to go a single mile.  That is what MSFT has done to Excel.  Not what I would call progress.

    Friday, December 02, 2016 5:55 AM
  • You are of course correct that Excel has got slower with recent versions when using your benchmark. And I found it very interesting to find out why.

    But there is a problem with your benchmark comparison between different machines and Excel versions. Unless all your machines had identical screen sizes and screen resolutions and you always ran your benchmark with the same upper left visible cell, your benchmark is comparing apples with oranges.

    I suspect that your more modern PCs have higher screen resolutions and hence more of your worksheet is visible and hence Excel's screen updating routine has to do more work.

    You asked how to make recent Excel versions run as fast as older versions. The main answer is to turn off screen updating.

    You asked why recent Excel versions run your benchmark slower than previous versions. The reasons (apart from from any possible inconsistencies from higher-resolution screens) are that adding additional function to Excel such as extended conditional formatting, and making Excel run across multiple end-points (Windows, Mac, Online, IOS, Android) has slowed down the screen updating routine. For your benchmark the negative effects of this far outweigh the benefits.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Wednesday, December 07, 2016 3:22 PM
  • Thanks Charles.  Makes sense that screen updating has been a drag on performance, it just seems like faster processors and memory should have more than compensated for that.

    As a test, I ran the code on my current laptop (Win10x64 / Office 2016 x64 / i7-7500U / 16GB RAM) with screen updating turned off, and it managed about 2400 loops/second.  That is a significant performance improvement versus 850 loops/second with screen updating on.  Unfortunately, it is still about 20% slower than a 2006-era Dell Vostro with a Celeron 420 processor and 1GB of RAM, and that machine had screen updating turned on.  So there is definitely more going on with performance degradations than can be explained by an increased screen updating burden.

    Wednesday, December 07, 2016 8:06 PM
  • Hi gurs,

    In my opinion, it doesn’t mean the performance of Excel 2016 declines. This issue doesn’t only matter about calculate power of Office itself.

    1. Due to the large changes in the background between Office 2003 and Office 2016, and compatibility issues between VBA and different Offices, it’s not recommended to use the same VBA code for all Offices. VBA code should be updated to calculate the performance for Offices of different versions. A document for your reference: https://msdn.microsoft.com/en-us/library/office/jj229903.aspx.
    2. Some formulas and objects may have different compatibility in legacy Office and Office 2016. It may also lead to the different calculate performance.

    Meanwhile, as MVP said there are much more Rows and columns in Excel 2016 than Excel 2003, so the number of calculated data is many times larger than it is in Excel 2003.

    In short, there are many factors can affect the Excel’s calculate performance, such as: The Excel content, the formulas and objects it contains, the VBA compatibility, etc.

    I suggest you to test same content with multiple different environments (different Offices, different OS), see if he will get the same result. And try to isolate the line of the code which is slow. Then determine why it’s slow in Excel 2016, and if there is any replaceable sentence with Excel 2016.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, December 08, 2016 1:41 AM
    Moderator
  • Emi, thanks for the reply, but you are missing the point.  I am not trying to troubleshoot my code.  The bottom line is that Excel is slower now than it was in the past doing the kinds of things that average users do with Excel.  A lot slower.  And that is a shame, and a problem.  Computers have generally gotten faster on a logarithmic scale, but Excel keeps getting slower.  You can blame that on how much bigger the worksheets are or on how many more cells fit on a screen, but the fact remains that it is SLOW.  If Microsoft cared about speed, then it wouldn't be so slow.  But they care more about features, hence the speed drop.  End of story.
    Thursday, December 08, 2016 4:39 AM
  • As I said earlier, screen updating is not the only thing causing performance differences between versions.

    Here are my numbers running your benchmark with all the Excel versions I have installed on my desktop machine: all data is loops/second at 50% zoom rows 1:120 visible, columns A:BF visible

    XL 2003 Screen on 847 Screen Off 15693

    XL 2007 Screen On 495 screen off 8343

    XL 2010 Screen on 503 screen off 8586

    XL2013 screen on 2378 screen off 3049

    Excel 2016 is only installed on my desktop machine in a VM so the numbers are not comparable but I got

    XL2016 (VM) screen on 1019 screen off 1228

    On my Surface pro 3 with XL 2016 (the only Excel version installed on the Surface) I get

    (zoom 50% rows 1:70 columns A:AJ visible)

    Xl2016 (Surface pro 3 I7) Screen on 2273 Screen off 3884

    I think its also worth noting that your benchmark is not typical of the way most people use Excel.


    Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

    Thursday, December 08, 2016 11:31 AM
  • If Microsoft cared about speed, then it wouldn't be so slow.  But they care more about features, hence the speed drop.  End of story.

    I agree with you wholeheartedly.

    But on the other hand, if you put the focus on the data and not all those eye catchers, it is still possible to write fast macros. And furthermore process more data as in older versions in less or the same time!

    Andreas.
    Thursday, December 08, 2016 2:25 PM