Improve speed of saving a large Excel file

E

Elmer

My client has an 6.5mb excel file. This file contains VBA codes. It takes
about 10 to 12 seconds to save the file on a P4 786mb computer. My client
claimed that it took about 2 seconds to save the file on a dual processors
computer. I am not sure what kind of dual processors computer he has.

Is there a way to improve the speed of saving the file?

Thanks,
 
J

JLatham

Elmer,

The CPU is not all of the equation. I'm thinking that if he has a dual core
cpu, either an AMD X2 series or an Intel Core-Duo (or even D series) then he
may also have a faster SATA type interface for his hard drives. SATA drives
can be anywhere from 2 to 6 times (or more) as fast as EIDE drives such as
probably came with the P4 system. Also, the clock speed on the dual core
system is probably a lot faster than on the P4 - so the dual-core CPU has at
least two advantages over the P4: it just plain runs faster, and being of a
later architecture, it gets more done during a single clock cycle anyhow.
Toss in SATA drives, and I can believe that the dual-core setup is faster
than the P4. But I'm not sure about his claim of 2 seconds at all.

Other factors: how full is your hard drive? A drive that is very full
and/or very fragmented can cause it to take longer to read or write a file:
the system has to hunt for places to put/get the pieces and physically move
the drive heads to those places. A drive with lots of empty space and that
is relatively unfragmented will give better performance also. Another
factor: the cache size on the disk drive itself. Because a disk drive is so
much slower than the CPU, it cannot physically write data to the drive as
fast as the CPU can send it to the drive. So drives have on-board cache
memory and a controller to act as a go-between for the CPU and the drive.
Older drives had on-drive cache memory usually on the order of 2 MB, while
newer drives can have 4 times that much ... and THERE may be where he gets
the speed advantage. If your drive has only a 2 MB cache, then it has to be
emptied and filled 3 or more times to get the 6 MB file to the drive. If his
drive has an 8MB or larger cache on it, then all 6 MB gets written "to the
drive" in one push.

But I've seen some strange behavior. I was working with someone else in
these groups and eventually we had a 6+MB .xls file to mess with. It would
take a minute or more for me to open it (on an AMD X2 4800+ dual-core setup
with 2GB RAM and SATA drives) and the person at the other end said it was
under 30 seconds for him, and yet his setup was "weaker" than mine, at least
on paper.

I kind of get the feeling that you're thinking that it's time to upgrade
computers? Don't let anyone else's claims of performance drive you into
junking a perfectly good system. Question #1 - is it getting the job done?
Question #2 - if you hadn't heard that there was something faster, bigger,
flashier, shinier, with a better warranty that they got 2 weeks before you
bought yours, and paid less for, would you be happy with what you have? If
the answers are Yes, then stick with what you have that works for you, is
paid for, and that you're generally happy with.
 
E

Elmer

Thanks for your detail analyze. Your comments give some supports to set a
limit on the performance testing of the excel file. There was not an official
benchmark for the excel file under test. It is based on the test result from
the person who creates the excel file.

I did some tests in this afternoon on two different computers. Both
computers have P4 3.0 GHz and 1GB memory. One has XP Pro and the other has
Windows 2003 Server. On the XP Pro computer, the file saving time is about
5-6 seconds. On the Windows 2003 Server computer, the file saving time took
about 20 seconds or more. I haven’t tested this file on a Core 2 Duo
computer. I started preparing a Core 2 Duo computer but I haven’t finished
it. I will test the file on that Duo processors computer.

We replace our desktop computers every three years. The production computers
used at this moment are less than two years old. They did a very good job for
most of the users.


Thank you,


Elmer
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top