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.
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.
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
- 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.
- 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.