Forum Discussion

ctz69's avatar
ctz69
Copper Contributor
Oct 14, 2022

Terrible recalculation speed on Ryzen processor

Something has happened recently to my copy of Excel 2021.  Perhaps it is an update, I'm not sure.  My large spreadsheet model now takes 40 seconds to recalculate on my Ryzen 7 3700x, a 16 thread CPU....
excel
Formulas and Functions
ctz69's avatar
ctz69
Copper Contributor
Oct 16, 2022
After more experimentation, I found that if I disable multi-threaded calculations in the advanced options page, the recalculation time goes from 40 seconds to about 1 second. I don't want to speculative on why this happens. Thoughts of bloatware are going off in my mind. I've checked the calculations and all are accurate. Needless to say, I've disabled multi-threaded calculations in Excel. Perhaps this will help someone in the same situation in the future.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 17, 2022

    ctz69 One thing to try is:

    - Open workbook

    - Set Excel back to automatically detect # of cores

    - Hit control+shift+alt+F9 (this forces Excel to rebuild the calc tree and do a full recalc)

    - Let Excel calculate the workbook again (F9), perhaps even multiple times

    - Now time the calculation, has it improved any?

     

    NB: If you open an Excel file which has been recalculated last on a different version of Excel, Excel may decide to do a full recalc the first time round. Subsequent calcs should be quick as usual

    • ctz69's avatar
      ctz69
      Copper Contributor
      Oct 17, 2022
      Tried this and recalculated 5 times and it ran at 36 seconds each time, down from the original 40 seconds. Turned off multi-threaded calcs and restored the 1-2 second calculation. Bug or working as intended? Maybe it doesn't work properly on AMD CPUs? Considering the processing power of modern CPUs, it must be a bug or bad design.
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 17, 2022

        ctz69 Thanks for trying. Let's wait and hear what the team has to say about this. I'll report back if I hear anything.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 16, 2022
    I've reported this to the Excel team.
    • fastexcel's avatar
      fastexcel
      Brass Contributor
      Oct 17, 2022

      Multi-threaded recalc occurs in 2 steps.

      Step 1 works out how to allocate the calc between threads

      Step 2 then actually does the calc.

       

      Usually the results of previous Step 1 optimisations are used as a starting point for subsequent recalcs so the time taken to do step 1 diminishes on subsequent recalcs.

       

      Difficult to make any sensible comments without looking at the actual workbook (would be happy to do that if possible)

       

      Charles Williams

Resources