Slow performance with large Excel files

[acole1]

The Configuration:
Vostro Notebook 1700
T7700 2.4Ghz
2x2GB DDR2 667
320GB SATA 5400RPM
XP SP2
Office 2007 SP1


The Problem:
Our CFO is getting irritated at the slowness of his machine, especially when dealing with some large Excel files that he uses a lot.

He gave me a demonstration yesterday with a 64MB Excel file that pulls financial information out of our ERP system via ODBC connection, and does some calculations.

I opened perfmon and let the default monitors run (% processor time, memory pages/sec, avg disk queue length).

Before he opened the file, everything was pretty much flat at the bottom, or <5%.

When he opened the file the CPU would generally hover around 20%-40% while it was opening, but everything else was still low.

Once the file opened (about 3 min later) the Memory Pages/Sec jumped to about 95% and sat there perfectly flat for the whole time the file was open.

I then refreshed the data, the CPU jumped up to ~50% utilization, and the Excel.exe process was steady at 50% CPU. I then set the affinity to one core.

The refresh process ran for almost 10min with Excel running at a solid 50% CPU and the System process jumping up to sometimes 40%, for a total CPU utilization of 90%+.

As the data was refreshing I first noticed the RAM used by the Excel file was about 512, then a couple min later it was 750 and climbing at about 1-2MB / sec.

The peak overall system RAM usage showed by task manager was about 1.5GB.

Once all the data refreshed the CPU dropped down, but the pages/sec stayed high.

I then closed the file, and the pages/sec dropped back down to <5%.

I opened just excel (no files) and everything stayed flat.

The disk usage stayed under 5% the whole time.


Solution?:
It was mentioned that perhaps the laptop should be upgraded to Vista Business 64bit, but I'm not sure that would help that much... but I'm not sure.

Would Vista and/or 64bit OS help at all in a situation like this?

I would recommend a desktop with a faster CPU clock speed like an E8400 or E8500. But he would lose his portability and it would cost more $$.

The main thing is that it's very important that this get sped up somehow.

What do you think the best thing to do would be?


Cliffs:
Large Excel files run slow on CFO's new laptop.
Seems to be CPU, but pages/sec shows strange activity.
How can it be sped up?

 

[dderolph]

Would Vista and/or 64bit OS help at all in a situation like this?

I don't know; I think I'd want to test that on a computer running that OS, if possible. Otherwise, you might be disappointed.

This seems a challenging situation. 64MB is a *HUGE* Excel file, so I'm not surprised that his computer is struggling with it. Could you test that file on a desktop computer in the office? That might provide evidence that such a large file will cause sluggishness on other machines, not just his notebook.

And, what about using Access, instead of Excel, for this financial information out of your ERP system? Access can do some calculations and, if will do the calculations needed here, it could be a solution. Spreadsheet programs have to load the entire file into memory while database programs do not. Thus, database can work better with huge amounts of data. Whether database would serve the purpose here depends on exactly what is required as far as doing calculations and other factors. And, of course, if the CFO has no knowledge of Access, some new learning would be necessary.

You said that, if the CFO had a desktop computer, he would lose the portability. But, could he have both a desktop computer and the notebook computer, and use the desktop for such tasks as these huge Excel files if, indeed, a desktop computer would make a significant difference.

 

[acole1]

We have a couple other PC's in the office running Vista and Office 2007, but they are all Vista x32 and have slower processors. I think this will be my next step though. It would really clear up a lot about it being the machine, or just the size of the file.

We had thought about using Access, but the reasons for sticking with Excel were:
1) The CFO is very good with Excel.
2) Our ERP DB can be changed on the fly with Access + ODBC, so there is some danger in that.

It seems like he doesn't do that much heavy work from home or out of the office, so it would probably work having a desktop and laptop. It just depends on whether or not he would like that solution or not.

I'll try to run the file on my computer then report back with the results.

Thanks for the reply!

 

[NeoV]

what kind of formulas are in the file - is it a straight ODBC query, or are there pivot tables/lookups along with that data?

 

[acole1]

Originally posted by: NeoV
what kind of formulas are in the file - is it a straight ODBC query, or are there pivot tables/lookups along with that data?

I'm not exactly sure what formulas are in the file, but I know it does more than just pull data from the ODBC connection.

I'm sure he has some rather complex lookups and formulas shifting and calculating data. It's incredible how much the guy knows about what he can do in Excel. He definitely pushes it to its max.

I didn't see the formula's but what I saw looked like a pretty complex worksheet, and there were many other tabs in the workbook that it was probably calculating as well.

 

[acole1]

I ran one of the files (64MB in size) on my computer and it ran pretty much the same as it did on the CFO's computer - still unacceptably slow.

My Configuration:
E6400 @ 2.13
3GB RAM DDR2 667
250GB SATA 7200RPM
Vista Ultimate 32bit SP1
Office 2007 SP1


The Situation:
I opened the Excel file from the network, it downloaded, then came up with a message about trusting the source, which I hit "Yes" on.

After that point then it proceeds to "open" the file.

I open perfmon and see that the file is at ~700MB and growing very rapidly, along with my total RAM usage.

Also, my total processor usage is at ~50%, with Excel running at 50%.

It takes a couple minutes to open, and the CFO was satisfied that it was not a problem with his machine, and that it was indeed the application.


Solution?:
My next step might be to try and find a 64bit version of Windows to run it on, or try it on one of our beefy 8x3.2ghz servers and see if it makes a difference.

 

[NeoV]

64bit version would make no difference

try turning the calculations from automatic to manual in the options section

ODBC pulls don't any stress on your CPU after they are done running

 

[MrChad]

Improving Performance in Excel 2007

 

[acole1]

Originally posted by: MrChad
Improving Performance in Excel 2007

Very nice link! Thanks!

 

[acole1]

Originally posted by: NeoV
64bit version would make no difference

try turning the calculations from automatic to manual in the options section

ODBC pulls don't any stress on your CPU after they are done running

Thanks for the tip. I'll have him do that.

One place it runs very slow is right when you're opening the file. It pushes a single thread to its max. The only way I can think to speed this up is a faster CPU.

 

[NeoV]

64 bit could help if you addded RAM though, you can use more ram in that version than the 32bit version

 

[acole1]

Originally posted by: NeoV
64 bit could help if you addded RAM though, you can use more ram in that version than the 32bit version

It has 4GB in it right now, but XP 32bit sees 3.5GB of it.

I read in MrChad's link that it looks like the max size for an Excel file is 2GB... it seems like whether or not it's 64bit (?)

Either way, our CFO's Excel files usually don't expand to more than 768 or 1GB in size.

 

[NeoV]

a 1 GB file in excel is crazy big

 

[PowerEngineer]

It sounds like your "power user" CFO may be pushing the practical limits of Excel. As others have suggested, automatic calculations on complex spreadsheets can take bog machines down -- particularly if they autonatically updating data (that triggers recalculations).

There is one thing you might try. In some cases, I have found that the Excel (XP) workbook used during development takes on some size/complexity(?) characteristics that aren't really required by the final version. I have sometimes "fixed" this problem by starting a new workbook and copy/pasting the spreadsheets (just the sections I need) and the macros from the development sheet. The new version might come out a lot smaller and faster.

It's a long shot...

 

[yuchai]

Agree with others that the workbook is pushing the limits of Excel. In my experience the only practical way around this is to turn off automatic updates as someone suggested and only recalculate when necessary.

This is clearly just a temporary solution, I believe the long term solutions are:
1. Move to Access as someone suggested
2. Break down the current workbook into a few smaller workbooks

Clearly these two solutions require your CFO to make adjustments to his work, so guess they're not real solutions on your end.

 

[NeoV]

if he's doing ODBC pulls with tons of data, Access would handle it better - or, if this data is coming out of a SQL database, you have some extract options via SQL query manager to filter out things before they get to Excel/Access

 

[gsaldivar]

I tend to agree as mentioned above, your CFO user is probably approaching the limits of what Excel is designed to do. Excel was designed to be more of a multi-purpose analysis tool, but it sounds like your user is running very specific, very large, compute-intensive analyses that are pushing Excel's performance limits.

It might be a good idea to explore other solutions that may be able to improve Excel's performance by altering the way your user's data is processed. It's important to note that this these techniques don't work on all types of numerical analysis, nor do they increase the speed of all calculations by a fixed amount.

http://www.microsoft.com/hpc/finserv/excel.mspx (Parallel/Cluster acceleration!)
http://www.scicomp.com/derivativesmodeling/integration
http://www.aspeed.com/products/Excel.html

There is also some very promising work currently being done to allow an available GPU (Nvidia, ATI, etc) to assist in processing. This method is also very dependent on the type of analysis that is being done, but in some specialized cases the performance improvement looks amazing (30-80x speed improvement over desktop CPUs for Monte Carlo pricing models).

http://www.scicomp.com/parallel_computing/GPU_OpenMP
http://forums.nvidia.com/index.php?act=ST&f=64&t=67720
http://benchmarkreviews.com/in...k=view&id=187&Itemid=1

Hope this helps.

 

[KentState]

We have many large Excel files (> 300 megs) and they don't have any of those issues.

You really need to explain how the spreadsheet is being populated. Is it a straight pull from SQL Server/Oracle/Other RDMS?

 

[acole1]

Originally posted by: KentState
We have many large Excel files (> 300 megs) and they don't have any of those issues.

You really need to explain how the spreadsheet is being populated. Is it a straight pull from SQL Server/Oracle/Other RDMS?

It's a straight pull from a Progress database via a direct ODBC connection.

The slowness is not when the data is being downloaded. It seems rather that the slowness is caused by a) the file expanding to its full size, and b) refreshing/running the calculations.

The file starts at ~9MB, but when you open it it expands to 768MB or more. That process alone seems to take about 10 min.

 

[TheEarthWillShake]

Try it as a 03 file instead as a 07 if you can or the other way around.

 

[gsaldivar]

Originally posted by: TheEarthWillShake
Try it as a 03 file instead as a 07 if you can or the other way around.

That might actually slow it down, since 07 is the first multi-threaded version of Excel.

 

[acole1]

'03 won't take as much data. The CFO needs the extra cells '07 gives you.

He might be able to break it up into smaller files to make it so it can be an '03 file, but it wouldn't tell us much because we already know it will be faster if it was in smaller files.