Excel increasing slowdown, in multiple versions

[rappleman]

I've Excel VBA macros which repetitively open 100+ data files (no more than 4 open at once), do heavy number-crunching, save results, then do it all over again a few hundred times with slight variations in parameters. Takes a week to run one. Using Timer function I discovered that the time taken to do one iteration slowly increases from 10 minutes to over an hour (even though the data and calculations are the same) and despite all effort to close files as soon as finished, etc. Using Excel 2000 (9.0) and read in Microsoft knowledge base there are known memory leak/slowdown bugs fixed by Service Release 1/1a, which I applied, to no effect. Then tried installing Excel XP. Found the increasing slowdown still there (and Excel XP runs much slower than Excel 2000, so I'm back with Excel 2000). No error messages generated and it eventually successfully finishes, but it just slowly slows to a crawl as it runs. OS is XP Home. Not machine-specific (tried it on a different computer). Restarting Excel (without rebooting) gets it going fast again. Any suggestions as to where to look for the problem, how to narrow down/diagnose/fix, etc.? Thanks!

[Paul Komski]

Hi rappleman some more information about you system would be useful. Particularly how much RAM and what are your VM settings; which CPU too please.

Excel is known to have memory leak problems and would not be my own choice for such prolonged number-crunching exercises. The whole "crunching" will slow down over time as any leaks appear and as any virtual memory is squeazed. A faster CPU would be of some benefit maybe but having maximum amounts of RAM and optimized VM would be more important.

We have no idea how big the arrays that are being manipulated are of course, but if you can keep them as small as possible that would also help, nor what type of mathematical transactions are taking place.

which repetitively open 100+ data files (no more than 4 open at once)

Note that if these files were opened and then closed (but without actually shutting down excel) that the memory pages allocated to them are not in fact cleared - and so this could be part of what is progressively slowing things down.

In short you need as much RAM as possible and then to manage your VBA in the most appropriate manner - (probably not using excel at all if you are, in fact, just manipulating a database).

[Variable]

I have a client who was generating huge .doc files in Office XP. His cpu was pegged at 100 percent and his machine would slow way down as he was working. He was pasting thousands of words at a time into a book, anyway, the problem was, he was going past the maximum file size for a document. Office XP has a compression algorithm that helps with file size limits and I believe the combination of pasting and the compression was eating his cpu cycles. The answer was to limit the file size, to try and keep it as close to 32 Megs as he could; when he did this his problem abated. Excel also has file size limits and formula limits. I would use ctrl-alt-del and watch the RAM and cpu usage on your XP machine. If your CPU is pegged (as his was) adding RAM will likely not help. In order to diagnose the problem you need to know what the hold up is, ctrl-al-del/task manager then performance tab will let you know. If your hitting the formula limit, then Im not sure you can fix it or If its a Excel file size limit problem the only way you can fix it is to use smaller file sizes or port the application to a program that doesn't have the limit.

Here is some info I found for reference
http://www.decisionmodels.com/memlimitsc.htm


Variable

[rappleman]

Thanks guys. Task mgr .bmp attached. You can't see CPU graph because monochrome (due to attachment size restrictions) but it's 0% > 1/2 time, 22%-100% the rest. If I'm reading the info correctly, it doesn't seem to be straining the virtual memory? Running on a 2.5ghz P4 with 512mb. The app is an investment model (backtesting different strategies on a database of securities). 75+ data files, each 1 month of historical data ~1mb 24 cols (variables) x ~4000 rows (securities). For each strategy the macro opens a monthly data file, performs heavy crunching math (not rocket science, but lots of it, and the spreadsheet format is the ideal representation) then selects securities based on the math done to the variables, saves the results for that month, closes the data file and goes on to the next month. When done with this it has the historical performance for that particular strategy and begins again with the next (slightly different) strategy. Initially takes ~10min/strategy but by the end taking 60min. All thoughts gratefully accepted! :confused: