Large scale engineering calucations performance bottlenecks

[Eric]

I am running into significant performance bottlenecks
running large-scale simulations on Excel 2003. Any
suggestions?

I am running a P4 laptop 2.4 GHz with 1GB of DRAM. My
workbook file has 7,000 rows x 10 columns of data, times
12 additional worksheets of formula each with 6,500 rows
x 10 columns which draw on the original data worksheet.
(And, no, I haven't found a suitable matrix solution for
this problem, although I'd love to.) This gives me a
150MB spreadsheet file, excluding solvers/optimization
macros. It takes 15+ seconds to change one of the
optimizing variables (there are 30). When I optimize, I
must change these variables repeatedly for a global
solution (the simulation is non-linear over most of the
range), and this will exceed 300 x 15 seconds = 90
minutes just to optimize. I need this to come down by a
factor of ten, because I plan to expand the data set from
10 columns to 100 columns -- this should increase the
file size to at least 1.5GB, and exponentially increase
optimization time.

Is there a product offered which will overcome these
issues? As I said, I am running a P4 2.4GHz with 1GB of
DRAM -- I should have enough physical memory, but Excel
2003 doesn't seem to let me use it, particularly for
calculations and macros. Is these a version of Excel
which is optimized for calculations/simulations?

I've obviously looked at solver.com, but the first issue
isn't the solver, but the calculation time even without
the solver.

I have looked into moving this calculation onto a
different platform (custom-designed database and
optimizer) but the issues remain: this simulation is best
designed for a spreadsheet, and having all the data in
memory optimizes speed. Thus, I feel a spreadsheet is
best for this application.

Anyone have experience with this type of modeling? Anyone
have any suggestions as to what I should do?

 

[JE McGimpsey]

You're going to need to get a better performance boost than 10:1. A
1.5GB file in 1GB RAM just ain't gonna cut it. For one thing, file size
does not correlate 1:1 with physical memory requirements - the latter is
significantly higher.

John Walkenbach's (j-walk) blog has recently had a series of posts on a
product that claims to give a 300 times performance improvement:

http://j-walkblog.com/blog/comments/D15438_0_1_0_C/

 

[Charles Williams]

Hi Eric,

You may be able to speed up the calculations significantly : depends what
excel formulae/functions you are using: give us some examples and we will
see...

alternatively you could transfer the calculations to VB, read the data into
an array/arrays, calculate using VB (compiled VB if its really heavy-duty
calculations) and transfer back to excel.

or you could switch to a different product like matlab or ...

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com