在Excel中多线程的计算速度慢 [英] Multithreaded calculation in excel is slow

问题描述

我不知道是否有什么办法来控制Excel的计算使得在WB几张并行重新计算，而其他人没有。我的问题是建立在当前下连续计算过程中，我有时间过长。顺序是从VBA驱动，是这样的：
1）VBA调用一个插件（Excel的DNA vb.net插件，控制一个DataReader和连接数据库）
2）插件数据返回到表1表2-3被重新计算顺序（表2，然后3）
3）表4到10的重新计算顺序，但由于各自独立地只链接表2，理论上他们可以重新计算并行 - 对吗？但如何做到这一点？
4）通过）

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)

在我的笔记本电脑上运行的数据子集（64位32位与Excel中，英特尔酷睿i7），它需要54.6秒。有趣的是，如果我关掉多线程，它需要25.8秒！更重要的是，如果我一个超快速的机器（2×英特尔至强X5570，四核Nehalem架构，64位与64位Excel）中它比我的笔记本电脑慢，服用多线程230秒或26秒内没有。上运行它。

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.

我不知道是否有更好的方法我可能会利用多内核和多线程的。慢一点似乎是Excel的重新计算，而不是插件的速度。任何建议AP preciated。

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.

编辑：
上面的描述有点简单化 - 在现实中，我也有一个反复的钙/粘贴瓦莱斯/重新计算直到达到一定要求的灵敏度每片4-10（迭代收益的运行过程中，只有然后就是计算出完整的表）。我相信，这使得当前运行的设计行不通下，世行计算。

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.

表4-10是相同的（除了硬codeD输入参数），并有彼此之间没有计算依赖关系。如果每个的计算过程（提到的迭代过程和表计算），可以并行，而不是按顺序进行，我认为整个过程会快很多。

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

运行我的整个数据通过过程中设置，因为它代表需要约16小时，这就是为什么我渴望找到一些方法来改善这一点。

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.

谢谢！

推荐答案

• 下面是一个很好的开始阅读有关Excel计算性能： HTTP：//www.decisionmodels。 COM / calcsecretsc.htm 。

您可以标记使用类似[ExcelFunction（IsThreadSafe =真）]属性的Excel-DNA功能线程安全的，尽管这听起来不像瓶颈在这里。这将允许这些功能可以同时评估。

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.

如果您i7处理器拥有超线程（因此报告了4核到Windows，但实际上只有两个真正的核心），那么就可以更快地线程的Excel的数量设置为核心数量（比如2），而不是线程数量的处理器可以运行（这将是Excel的默认 - 也许4）

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

这篇关于在Excel中多线程的计算速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！