I have just implemented measuring multi-threaded calculation efficiency in FastExcel V3 Profile Workbook: so I thought it would be interesting to see the effect on calculation speed of varying the number of threads, and switching off hyper-threading.
Hyperthreading
Almost all of todays PCs have multiple cores. My desktop has an Intel I7 870 chip. This contains 4 hyper-threaded cores. Hyper-threading is an Intel hardware feature that allows each core to look to the software as though it is 2 cores. If one of the hyperthreads on a core gets stalled waiting for data the other hyperthread takes control of the resources and tries to continue. How efficient this is is highly dependent on the software application, and I had no idea whether Excel made good use of hyper-threading or not.
Because hyper-threading is a hardware feature you can only switch it off or on using the BIOS.
Excel Multi-threaded Calculation
Microsoft did a great job of implementing multi-threaded calculation in Excel 2007. The way this works is
- Excel analyzes the calculation chains of formula dependencies looking for lengths of chain that can be processed in parallel
- Starts a number of separate calculation threads
- Assigns the lengths of chain to the calculation threads for processing
- And finally gathers everything together
How successful this process is depends very much on the structure and linkages between your formulas, whether the functions you are using are multi-threaded etc, and this can vary enormously between workbooks.
You can control the number of threads that Excel assigns to the calculation process using File–>Options–>Advanced Options–>Formulas. The number of threads assigned can be larger or smaller than the number of logical cores available.
The Tests
I picked three largeish workbooks:
- TTG – 4.7 million formulas using 704 Megabytes of workbook memory
- GL – 466 thousand formulas using 114 Megabytes of workbook memory
- PF2 – 284 thousand formulas using 700 Megabytes of workbook memory
I used my main desktop machine with Windows 7 32 bit and Excel 2010 32-bit.
This PC has an Intel Core I7 870 2.93 GHZ with 4 hyper-threaded physical cores, so 8 logical cores.
I ran a full calculation on each of the 3 workbooks using 1 to 8, 12, 24, 64 and 256 calculation threads with hyper-threading enabled. Then I switched off hyper-threading and ran a full calculation with 4 threads (so matching the physical cores) and 8 threads.
Timing Results with Hyper-threading enabled
Timings are in seconds for the second or third successive full calculation (to enable comparable optimization of the calculation process).
As you can see, all three workbooks show different characteristics:
- TTG and PF2 are both fastest with 8 threads but GL is fastest with only 3 threads.
- Increasing the number of threads beyond the number of logical cores available slows down calculation, but the increase is much less marked with PF2 than with the other 2 workbooks.
- Increasing the number of threads beyond the number of physical cores up to the number of logical cores improves speed for TTG and PF2: so hyperthreading is successfully exploited by Excel. But physical cores are much more effective than logical cores.
The effect of disabling Hyperthreading
When I disabled hyperthreading using the BIOS:
- Calculation using 4 threads ran slightly faster for TTG (88 vs 93) and PF2(7.6 vs 7.7) but slower for GL (0.49 vs 0.48)
- Calculation using 8 threads ran slightly slower for all 3 workbooks (TTG 89 vs 73, PF2 7.7 vs 6.5, GL 0.58 vs 0.55)
So the effect of hyperthreading is noticeable but not large.
Running out of memory
There have been threads in the newsgroups about multi-threaded calculation causing Excel to give warning messages about running out of resources whilst calculating. I have not been able to duplicate this problem even with a large workbook like TTG running with 256 threads. The suggested solutions are either to turn off hyper-threading using the BIOS or to reduce the number of threads from 8 to 6.
Conclusions
- Excel’s multi-threaded calculation can be very successful at reducing calculation times.
- The effect of hyper-threading is not as large as that from multiple physical cores, but its still worth having.
- The effectiveness of multi-threading is very dependent on the workbook. There will be workbooks where the overhead of analyzing the calculation chains outweighs the gain in calculation speed.
- Excel’s default setting to assign a calculation thread to all available logical cores seems sensible.
Excel and UDF Performance Stuff 
Charles, do you know whether Excel 2013 can actually make use of the GPU (indeed multiple GPUs) as hinted in the Excel 2013 option to “disable hardware graphics acceleration”, or if Excel 2013 is limited to just CPU calculation.
Given the processing power available in GPUs, and especially when you have more than one GPU, this seems like the next holy grail in Excel calculation speed.
Excel does nor itself make use of GPUs for formula calculation. It is possible to code UDFs to make use of GPUs but I think this only makes sense for special types of calculation that have a high compute to data ratio and can be done in a massively parallel way.
I’ve actually developed a tool which does what Jonathon Power suggested. I’d love to be able to benchmark it against your tests (TTG, GL and PF2) for curiosity’s sake. Let me know if you’re willing to share the spreadsheets
Sadly I can’t share the spreadsheets.
What method are you using to to speed up calculation?
Ok thanks Charles for confirming that.
Wow! I’ve finally found someone who appears to understand the solution to the issues I’ve been having.
Would you be interested in seeing one of the spreadsheets which has the issue of running out of resources? I have a super powerful computer with twin Xeon multi core processors (24 cores in all) yet my relatively small spreadsheet (11Mb) won’t run successfully with all cores enabled.
Its a real struggle for me, and I would be very interested if you were able to offer me a solution to this.
@Mike,
Yes I would be very interested in looking at your spreadsheet: I have not been able to duplicate the problem on my system.
Hi,
Based on Mike’s question, have you been able to reproduce and solve this issue? We have a similar case like Mike’s one. Our spreadsheet is even smaller in size. It’s a file wich has around 7 or 8 files linked through formulas. To refresh the main file, we need to have all those files open and when you start to move through the files and use the filtering, then it may start to behave strange and finally it blocks excel completely.
Does this look familiar and can this be solved with the Multi-threaded calculation setting as described above?
Many thanks for your feedback
Kris
Hi Kris,
I never got Mike’s workbook to test, but the reported problem was an error message about insufficient resources whilst Excel was calculating. This is not the same as getting that kind of message because of large edits etc: don’t know which fits your case best, but you could always try switching off multi-threaded calculation to see if that solves the problem.
Kris,
I have a spreadsheet I update monthly for work. It’s only about 6-7 MB, but it has a lot of resource heavy formulas (Sumifs and array formulas written to give me Maxif and Minif). It used to crash all the time and my best recollection says the error was “excel ran out of resources”. About three months ago, I found a tip online that suggested turning off HT. I tried it, and it never crashes any more. I realize I might have slightly slower performance overall, but it seems worth it to not have to do small pieces one at a time to avoid the crash.
If you see potential value in seeing my example, reach out to me. Brent.Taylor (at) imaginelearning (dot) com.
Intriguing. For a while now I have been running financial simulations on my trusty laptop with a quadcore i7 2820 QM CPU. As some of these were taking up to 6 hours to run I thought a would invest in a new PC with a top of the range 5960X Haswell -E. The logic being that I had seen how much faster my laptop was than a higher clock speed dual core processor PC analysing the same spreadsheet. So I though with 16 virtual cores my simulation program would fly. Imagine my horror when it was slower with my shiny new CPU! I tried all sort of tweaks but by trail and error found that the spreadsheet calculations worked best with 9 processors ticked in the advanced options drop down menu. This has resulted in about a 50% improvement in processing speed. Cant explain it.
I’ve actually developed an Excel add-in that speeds up the sheet calculations massively (factors of 10x or more). I would love to have a shot at your ‘6 hour’ and share the results with you, provided you’re comfortable sharing the sheet with me of course 🙂
I know this is a very old thread, but I have a strong suspicion as to why this was the case. A lot of excel calculations are only able to make use of a single thread. If the calculation cannot be parallelized, then you’ll see no benefit from more cores or more threads. Therefore, you are limited by the instructions per clock (abbreviated IPC) or speed of that thread. A 5960X has 8 cores, but they do not have a particularly impressive clock speed (3-3.5 GHz). As such, for all single-threaded calculations, a processor with a higher IPC (like an Intel 6700K with a clock speed of 4-4.2 GHz) will be much faster. It is important to note, however, that it’s not possible to make direct comparisons between the clock speeds of chips with different microarchitectures. In any case, if you watch the resource usage in your task manager and see only one thread being utilized for the majority of your calculation, the number of cores will likely be less relevant to the speed of your calculation. On the other hand, if you see all threads being fully utilized for the majority of the calculation process, then it is much more likely you could benefit from having more threads (like in the case of your eight-core chip with hyperthreading).
Hi Boyan, are oyu able to share your excel add-in as I have a spreadhseet that takes about 2 hours to calculate and would love to say some time !!
Thanks
Steve
Have you tried FastExcel?
http://www.decisionmodels.com/fastexcel.htm
I know its almost two years later but just an FYI – excel has always had problems running formula’s that link to other files/workbooks on your local machine/network. Its not a resource issue its a excel opening all of those workbooks up and then having a 100 levels of undo and a few things running.
The thing is that its not just a question of running 64x and having lots of ram and CPU flops. Those things are good to a degree but I have found that after 8GB the performance of having extra ram bottoms out. Its almost as if excel cannot use ram space above 8GB. I ran a 16GB excel machine for a few years and it really didn’t offer any improvement over the 8GB machine I have at the moment.
In fact the best improvements came from having a SSD and a better/faster CPU.
Thus the best solution for the multiple file issue is simply don’t do it. Aggregate those files/data in access (or excel even), import the data across into your workbook.
If you don’t want people looking at it simply hide the sheet or protect it.
Hyper-threading the cores in your CPU improves performance and speed on a case by case basis depending on which tasks are compatible with a hyper-threaded core. If you’re looking to enhance your PC’s overall processing prowess, hyper-threading could be a step in the right direction.
I’m not sure where excel implodes these day but I found that limiting each workbook to 100 sheets was just under my 113 implosion threshold. Therefore, I am forced to run multiple linked workbooks. Great help reading this but it is tough finding a super superior per thread processer.
using an old Intel Xeon E3-1290 V2 @ 3.70GHz. The most I seem to see as a bump is 50% higher after 6 years when researching single thread improvement with Passmark. . What happened to Moore’s Law?