Multithreaded calculation in excel is slow

Question

I'm wondering if there is any way to control excel calculation such that several sheets in a WB are recalculated in parallel, while the others not. My problem is that under the current set up the sequential calculation process i have is taking too long. The sequence is driven from VBA and goes something like this: 1) vba calls an addin (excel-dna vb.net addin, that controls a datareader and attached database) 2) addin returns data to sheet 1. Sheets 2-3 are recalculated in sequence (sheet 2, then 3) 3) sheets 4 through 10 are recalculated in sequence, but since each independently only links to sheet 2, they could in theory recalculate in parallel - right? But how to do this? 4) begin process again with calling of new data via vba addin described in 1)

Running a subset of data on my laptop (64bit with 32bit excel, intel i7) it takes 54.6 seconds. Interestingly, if i switch off multithreading, it takes 25.8 seconds! What's more if i run it on a super fast machine (2 x Intel Xeon X5570, quad-core “Nehalem” architecture, 64bit with 64bit excel) it's slower than my laptop, taking 230seconds with multithreading or 26 seconds without.

I wonder if there is a better way i might make use of the multiple cores and multithreading. The slow bit appears to be excel recalculation rather than the addin speed. Any suggestions appreciated.

edit: The above description is a bit of a simplification - In actuality, i also have an iterative 'calc/paste-vales/recalc' process that runs on each of sheets 4-10 (the iteration proceeds until some required sensitivity is reached, and only then is the full sheet calculated). I believe this makes running a wb calculate under the current design unworkable.

Sheets 4-10 are identical (except for hard-coded input parameters) and have no calculation dependencies between one another. If the calculation process (mentioned iteration procedure and sheet calc) of each could be done in parallel instead of sequentially, i think the total process would be much faster.

FYI, i already performed the more obvious streamlining and testing (breaking up formulas, separating volatile functions etc).

Running my entire data set through the process as it stands takes about 16 hours, which is why i'm keen to find some ways to improve on this.

Thanks!

Answer 1

• Here's a great start to reading about Excel calculation performance: http://www.decisionmodels.com/calcsecretsc.htm.

• You can mark an Excel-DNA function as thread-safe using an attribute like [ExcelFunction(IsThreadSafe=true)], though that doesn't sound like the bottleneck here. That will allow these functions to be evaluated concurrently.

• If your i7 processor has hyper-threading (so it reports 4 cores to Windows but actually has only two real cores), then it can be faster to set the Excel number of threads to the number of cores (say 2) instead of the number of threads the processor can run (which would be the Excel default - maybe 4).