Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So I have a computer with 3.45 GB of RAM according to my OS (XP). I have 2 GB virtual RAM.

My company pushes Excel to its limits of referencing other worksheets which eats up a lot of resources. Often times when it is running calculations, saving, etc the program will appear to hang for sometimes as much as 10 minutes.

When looking to see what the issue is I notice the processor is usually running at about 8-12% but the RAM is usually 250,000+ KB. This causes my computer to run slowly which would make sense to me if I only had 512 or maybe a Gigabyte of RAM. I know that 250,000 is quite a bit of memory to be used for the OS, but having a total of 6 GB, I would expect much better performance.

Is there an explanation as to why it would run so slow?

edit: I should clarify. I'm looking for more of the concept behind this not just a solution to my computer running slowly (that was just supposed to be an example). I was under the perception that I should be able to use nearly all my RAM before noticing serious speed changes that would affect my whole system.

share|improve this question
    
What processor do you have? Also, 4GB of ram is not that much. Average at best, but it's the bare minimum recommendation I would make to anyone today. –  Darth Android Jul 25 '11 at 20:56
    
It's hardly "average at best" for a system running XP 32-bit. –  Joe Internet Jul 25 '11 at 21:10
1  
@Joe Relation to the maximum capacity of the OS is irrelevant-- only relation to the number of applications the average user uses these days, and how much memory they take up. True, adding more will have no effect until he upgrades to a 64-bit OS. –  Darth Android Jul 25 '11 at 21:19
    
IMO, you can't remove the hw context from the "average". OP is running Windows XP, which has a reommended minimum RAM of 128M, with a supported minimum of 64M. So the roughly 55x the supported minimum is probably above average. –  Joe Internet Jul 25 '11 at 21:41
add comment

5 Answers

up vote 9 down vote accepted

Might some of these referenced spreadsheets be on a LAN? If so, what sort of connection do you have to the files on the LAN (100mbit, Gigabit, wireless, etc)?

Excel is very "chatty". Especially when you're dealing with reference spreadsheets and data spread out across various resources, it can take a lot of communication for the application to send and receive necessary information. If most of the files are on network shares and network drives, this may explain at least a chunk of the slowness you're experiencing.

Further, regardless of how much memory you're using, you'll probably find excel.exe usually doesn't take as much memory as you expect it to take. Are there other processes that are running on your computer that aren't necessary? Lots of taskbar icons (next to your clock) that you have no idea how they got there or what they're for?

Finally, lots of companies leverage Excel far beyond it's best functions, like it seems your company does (why, oh why don't they use a database when they need a database and leave Excel to the simple data recording and processing functions????), I've found that comparing the speed of Excel on our older/slower machines (4 year old IBM desktops 8215 and 9645) and newer/faster machines (6month old desktops, 3269, 9964), and there is no significant difference.

Depending on how the macros and code connecting the spreadsheets is written, there may be what are essentially hard limits to how fast that spreadsheet can collect and process the data it needs.

All you can do is identify and mitigate all the possible points of slowness you can, and hope for the best.

share|improve this answer
    
Wow you nailed it! I had just figured that out with the help of the previous post and saw this answer afterwards. And yes I agree about the database. I was told the reason was "because we don't want to have to pay a DBA". Instead they pay me to spend 3 hours a day twiddling my thumbs waiting for Excel. I'm sure part of the issue could be the VBA since I'm primarily self-taught too but what I'm noticing is probably more of a LAN issue. Thanks! –  Zombian Jul 25 '11 at 21:34
2  
You're very welcome. The good news is that there can be relatively efficient ways to do things in Excel. A common task is finding the number of rows of data. Some people will search to a preset maximum for all rows of data, assuming that other ways risk reporting the last row when they encounter the first empty cell, not realizing there are quicker ways than manually checking an entire range that don't fail at the first empty cell. Another good practice in excel coding is loading entire tables into arrays, which keeps them local in memory. Small things like this add up and help speed things. –  music2myear Jul 25 '11 at 21:39
1  
Spend those 3 hours a day learning SQL & database theory, and how to improve your Excel VBA skills. While having a dedicated DBA is good for the very difficult stuff, or the very big iron stuff, it's not too hard to get going with something like SQL Server Express edition. –  Joe Internet Jul 25 '11 at 21:51
1  
@Joe: Or Access (which would be more friendly to the company because they probably all already have it installed, but much easier than Excel to migrate to SQL Server someday) –  BlueRaja Jul 26 '11 at 0:55
    
I can't think of a single benefit that Access would bring to this situation, other than the ability to draw buttons on forms. If you really need that, Visual Studio Express is still a better choice. –  Joe Internet Jul 26 '11 at 1:51
show 1 more comment

Windows XP doesn't do a very good job of using more than around 1GB of RAM. Some applications may use more, but no single application can use more than 2GB at a time, at least by default - that's the largest virtual address space an application can see. There's a mode that allows a 3GB address space, but that can cause compatibility issues for a lot of software.

If your machine is only using 0.25GB, that seems pretty wierd - unless memory isn't your bottleneck at all.

If your machine "appears to hang", my guess would be that you're doing a lot of hard disk access. That may make the Windows XP memory use issue relevant - Windows 7 is much more aggressive about predicting what you'll use in the near future and caching it. Windows XP won't even use that much memory for the cache, let alone try to do predictive caching, so you're much more likely to "thrash" the hard disk.

If that's the problem, some options to consider are...

  1. Try using a ramdisk, to minimise the use of the harddisk. Put the documents there, and ensure any temporary files are also stored there. There are even some ramdisks available that will use RAM outside of the 4GB that 32-bit Windows XP can access.

  2. Use a raid array with striping, to improve your hard disk performance.

  3. Use a solid state hard disk - but be aware that if your application is doing a lot of writes, that may cause excess wear and tear for that SSD. Also, Windows XP isn't optimised for SSDs - look out for updates to support Trim.

Basically, the idea is either (1) to force your system to use RAM, or (2, 3) to invest in hardware that will reduce your performance bottleneck.

share|improve this answer
1  
+1 for taking disk I/O into account. –  TFM Jul 25 '11 at 21:28
    
Thank you! I use a roaming profile and this made me realize that the read/write to the server for the files I'm working on is probably a large part of the issue. Also, well documented! Thanks again. –  Zombian Jul 25 '11 at 21:29
add comment

It sounds like you are using a 32 bit operating system (you don't say for definite in the question), in that case you are limited to the 3.45GB of addressable memory. Windows simply doesn't know about any memory above this limit.

This means that when running programs that use a lot of resources you'll be paging to disk much of the time as the OS has to swap out the data in memory with that on the disk. You could try increasing the size of your page file (assuming that's what you mean by virtual RAM), but you'd be better off letting Windows manage this.

You will need to install a 64 bit operating system to gain any performance benefits from your memory.

share|improve this answer
1  
do you mean to say that even if i install a 8GB ram on my 32 bit box i will only have 3.45 GB? and the rest will never ever be used? –  Pacerier Jul 29 '11 at 17:32
1  
@Pacerier - something like that. There's a 2GB limit per process and the limit is due to the nature of the OS - 2 raised to the 32nd power is 4GB so you can't go higher. –  ChrisF Jul 29 '11 at 21:30
add comment

I suspect something totally different than the current answers: I am wondering if you have the "Microsoft Office File Validation for Office 2003, 2007 Office, and Office 2010" update installed.

It brings network access of Excel files down to a CRAWL. It took me about 1 minute just to open a 21k file. As soon as it comes off, bang!, back to normal.

See my post here on Server Fault, and the associated KB article, which notes this as "known issue".

http://serverfault.com/questions/286427/slow-opening-of-excel-files-on-the-network

share|improve this answer
    
Wow! That sounds intense! Fortunately I'm running Excel 2010 and I also have been noticing this for months (the post said the update was at the beginning of this month). Thanks for the heads up though. I wish it ended up being that simple! –  Zombian Jul 25 '11 at 23:07
    
@Zombian OK, at least you checked. Yes, it does not affect Excel 2010 as the problematic functionality of the update is built in, but not goofy as for 2003/2007. That update caused a lot of professional admins a lot of time and hair pulling. I also wish it had been that easy for you. –  KCotreau Jul 25 '11 at 23:15
add comment

Other answers mention things that may contribute to your issues, but none of them seem to mention that Excel has built-in memory limits; it will not use all of a PC's RAM.

Here are some of the limits Excel has:

  • Excel 5: limited to 16MB
  • Excel 95/97/2000: limited to 64MB
  • Excel 2002: limited to 128MB
  • Excel 2003: limited to 1GB working set, less for actual workbooks
  • Excel 2007: limited to 2GB working set, less for actual workbooks
  • Excel 2010: limited to 2GB in 32-bit Excel, 8 terabytes (8000GB) in 64-bit Excel

Source and more info: Excel Memory Limits, by Charles Williams, Microsoft Excel MVP

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.