Time required to 'save as' a large file

[mleisure]

Hi!

I have a user who has a 14M workbook which has 26 worksheets. Each of the
worksheets has many (up to several hundred) large calculations. If you open
the workbook, work on it and save it to the same location it takes a minute
or so to save. If you do a file save and save it in another location, either
locally or on the network, the file takes up to twenty minutes to save. We
removed the Recalculate before save option hopes that it would speed things
up but it makes no difference.

Does anyone know 1) what cause this prolonged save time and 2) is there any
way to speed it up. This problem occurs on several of their large workbooks.

 

[Tom Hutchins]

If I'm wrong in my explanation, I hope an MVP will correct me. My
understanding is that when you open a workbook, you are actually working with
a hidden copy of the workbook that Excel creates in the same folder as the
original. When you save & close the workbook (successfully), the original
file is deleted and the copy is renamed to take its place. That's why it
saves quickly in the same location. When you Save As somewhere else, Excel
has to actually write out the entire workbook to disk from scratch. The
obvious solution is to copy the workbook from the original location to the
new location, either before working with it or afer saving & closing it.

Hope this helps,

Hutch

 

[mleisure]

Hutch,

Thanks for your information. We thought something along those lines was
happening but could not verify it.

 

[joeu2004]

When you save & close the workbook (successfully),
the original file is deleted and the copy is renamed
to take its place. [...] When you Save As somewhere
else, Excel has to actually write out the entire
workbook to disk from scratch.

I agree that that explains why Save As takes longer than Save.

But MKL said that Save As is taking "twenty minutes" for a 14MB file,
with "Recalculation Before Save" deselected and regardless of whether
the target is a local or networked disk.

I would say that "twenty minutes" is excessive for any file copy from
one location to another on a local disk. That's a read/write rate of
less than 12K per sec.

All bets are off across a network, since MKL did not offer any hints
about the topology. But I would say that "twenty minutes" would be
unexpected for most local network configurations, unless the network
(or target disk) was being flooded with traffic at the time. But in
that case, I would expect the "twenty-minute" Save As time to be
intermittent.

My suspicion is that the facts of the circumstances are incorrect.
(In the words of "Gregory House", "everbody lies" ;->.) Exactly which
facts are incorrect is anyone's guess. But my money is on the
contention that "Recalculation before save" is always deselected.


----- original posting -----

If I'm wrong in my explanation, I hope an MVP will correct me. My
understanding is that when you open a workbook, you are actually working with
a hidden copy of the workbook that Excel creates in the same folder as the
original. When you save & close the workbook (successfully), the original
file is deleted and the copy is renamed to take its place. That's why it
saves quickly in the same location. When you Save As somewhere else, Excel
has to actually write out the entire workbook to disk from scratch. The
obvious solution is to copy the workbook from the original location to the
new location, either before working with it or afer saving & closing it.

Hope this helps,

Hutch

Hi!

I have a user who has a 14M workbook which has 26 worksheets. Each of the
worksheets has many (up to several hundred) large calculations. If you open
the workbook, work on it and save it to the same location it takes a minute
or so to save. If you do a file save and save it in another location, either
locally or on the network, the file takes up to twenty minutes to save.We
removed the Recalculate before save option hopes that it would speed things
up but it makes no difference.

Does anyone know 1) what cause this prolonged save time and 2) is thereany
way to speed it up. This problem occurs on several of their large workbooks.

 

[mleisure]

joeu2004,

The network was tested and there were no problems found. However, when
saving the file on a local drive, using Save As, the save did take up to
twenty minutes. THis process was times and tested on multiple pcs.
--
Thanks - MKL

When you save & close the workbook (successfully),
the original file is deleted and the copy is renamed
to take its place. [...] When you Save As somewhere
else, Excel has to actually write out the entire
workbook to disk from scratch.

I agree that that explains why Save As takes longer than Save.

But MKL said that Save As is taking "twenty minutes" for a 14MB file,
with "Recalculation Before Save" deselected and regardless of whether
the target is a local or networked disk.

I would say that "twenty minutes" is excessive for any file copy from
one location to another on a local disk. That's a read/write rate of
less than 12K per sec.

All bets are off across a network, since MKL did not offer any hints
about the topology. But I would say that "twenty minutes" would be
unexpected for most local network configurations, unless the network
(or target disk) was being flooded with traffic at the time. But in
that case, I would expect the "twenty-minute" Save As time to be
intermittent.

My suspicion is that the facts of the circumstances are incorrect.
(In the words of "Gregory House", "everbody lies" ;->.) Exactly which
facts are incorrect is anyone's guess. But my money is on the
contention that "Recalculation before save" is always deselected.


----- original posting -----

If I'm wrong in my explanation, I hope an MVP will correct me. My
understanding is that when you open a workbook, you are actually working with
a hidden copy of the workbook that Excel creates in the same folder as the
original. When you save & close the workbook (successfully), the original
file is deleted and the copy is renamed to take its place. That's why it
saves quickly in the same location. When you Save As somewhere else, Excel
has to actually write out the entire workbook to disk from scratch. The
obvious solution is to copy the workbook from the original location to the
new location, either before working with it or afer saving & closing it.

Hope this helps,

Hutch

Hi!

I have a user who has a 14M workbook which has 26 worksheets. Each of the
worksheets has many (up to several hundred) large calculations. If you open
the workbook, work on it and save it to the same location it takes a minute
or so to save. If you do a file save and save it in another location, either
locally or on the network, the file takes up to twenty minutes to save. We
removed the Recalculate before save option hopes that it would speed things
up but it makes no difference.

Does anyone know 1) what cause this prolonged save time and 2) is there any
way to speed it up. This problem occurs on several of their large workbooks.