3

I have an 8 CPU 3GHz Windows machine that is estimated to finish a calculation using Excel in 16 days based on a sample data test. This performance is not acceptable(!).

The calculation does not use the capabilities of Excel but is basically a VBA script calling a COM object which has a Calculator function.

The Calculator Process is using 25% CPU and Excel is using 1%. Swap memory does not seem to be used. Since the process is reading and writing to text files, I assume that it is I/O related. I/O read/write counts are counting constantly during the calculation.

A virus checker is installed, but it is not actively checking. i.e. CPU is 0% during the calculation period.

The best suggestion so far, is to use a RAM Disk.

Could you suggest any other avenues of investigation into the performance bottleneck?

[Later...]

Many thanks for the advice below. Basically the Calculator Process, is a specialized Financial Calculator but as mentioned below it looks like the bottleneck is using it via COM. This method is removing all the multithreaded functionality that is offered by the calculator, which can also be addressed as a web-service.

The real-world scenario that is involved is a once in three year financial table update using this new Calculator. Unfortunately not enough time was reserved in the early enough in the schedule for performance estimation thus resulting in the urgency of investigation of the performance problem. Since this calculation is not done on a regular basis, there is no requirement to set up an environment optimized for speed.

The correct solution to scrap Excel and write a multithreaded dispatcher to the Calcualtor is apparent but we are currently looking for some workaround that we get the calculations done in a reasonable amount of time.

1
  • Would increasing the Priority of the processes involved help (say through Task Manager)? Jan 15, 2010 at 7:18

5 Answers 5

4

You are using the 25% of the CPU because the COM object is single threaded and does not use the other cores of your CPU. You can not increase the performance of your calculation without modifying the COM object code.

Sorry.

4
  • Would it help to create multiple instances of the COM object/and or Excel and hope the system schedules it across multiple CPUs? Jan 15, 2010 at 7:44
  • I the set of data can be split between the different instances it will work. Jan 15, 2010 at 7:48
  • 1
    @Matthew: If you are going to do that, I think you would be better off looking into other solutions, such as a proper programming/calculations environment (like the ones Wil suggested)
    – Josh Hunt
    Jan 15, 2010 at 7:50
  • 1
    @joshhnut: stuart says the Excel engine (actually the VBA engine) is only calling a COM object, so the engine used to do the call is pretty irrelevant because the calculations are done by the COM object. Take in mind that VBA and VB6 share more or less the same engine. Jan 15, 2010 at 13:54
8

Simply put, do not use Excel for this sort of thing. You may be able to look at Excel Services (add-on to SharePoint) which is meant to off-load processing, however I have no experience with it.

If you are doing something that complicated, you really want to look into serious mathematical tools that can scale to multi-CPU environments - However, I wouldn't even know where to begin without knowing what you are trying to accomplish.

Some things to look at include SageMath, MATLAB and Mathematica, amongst MANY others.

2
  • 1
    +1 Matlab is a great tool, but it's also possible that many general purpose programming languages would also be suitable (and free to use). Also, probably a lot of the CPU time here is simply Excel refreshing what is shown on the screen after every step.
    – sblair
    Jan 17, 2010 at 1:21
  • @sblair can't agree more. The first thing that came to mind was Python's interactive shell when I saw this question actually.
    – user1931
    Jan 18, 2010 at 10:09
4

One thing to do would probably be to find a better way to implement your software, using tools more suited to performance computing, or even a language/framework that is specific to your problem domain. Many tools are out there, i.e. Mathematica, SAS, R, and on and on. In addition, you should parallelize it as much as possible.

Also, avoid using the disk as much as possible. If you are going to use disk, beyond RAM disks you should consider using a high-performance raid array like a RAID 10, or even better a RAID 10 composed of SSDs.

You should profile your process while it runs and see how much is system time and how much is user time. I'm willing to bet that much of the execution time is taking up in system calls to perform all that disk I/O. Get rid of it.

0

You just need Task Manager:

Go to the Processes tab, then menu View / Select Columns, and check either or all:

  • CPU Usage (gives percentage of total CPU in use)
  • Memory - Working Set (memory in use)
  • i/O Reads + I/O writes (counts total number of disk accesses done)

You can then click twice (not double-click) on a column to have it sorted in descending order of values, so as to check for excessive usage.

However, if the problem is in the ActiveX object, then optimizing Excel wouldn't help at all.

0
0

As others mentionned, you should investigate moving your processing away from Excel. VBA and COM objects are not efficient at performing lots of back-and-forth operations.

Personally I would tend to send my data to a SQL server and write a query there. Your query/procedure can perform its calculations and provide the output to Excel using an external connection.

RDBMS solutions are quite efficient at doing things I would never dream of trying to do in Excel or VBA, working easily with multi-GB databases and as many threads as you can get your hands on.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .