Preformance problems

K

Keith

I use Excel a lot to access in Informix database through ODBC. I currently
have a file this is causing problems.

The file is 179Mb in size and has one sheet containing the data from
Informix which has about 14500 rows. On top of that there are 9 - 10 pivot
tables all relating to this data.

Once a month I refresh the data then go through each pivot table and update
it. My problem is that after updating the data and 3 or 4 tables the system
hangs. The CPU has 51% usage by Excel and 49% free.

If on the other hand I do 2 or 3 tables then save the spreadsheet and close
Excel then re-open the file it carries on fine.

Why is this happening? I thought that it was due to lack of RAM but I have
1GB. Is there a setting I need to change or something?
 
M

macropod

Hi Keith,

The problem here may be due to the fact that Excel is trying to store the
data refresh and pivot table updates in its undo buffer, just in case you
change your mind. When you save and close the file, that flushes the undo
buffer an lets Excel start over again.

Cheers
 
M

Marcelo

Hi Keith, I am not sure if its a good answer, but, are the Pivot Table makes
referense for the data or the Pivot table makes referense for the other pivot
table?

hope this helps
regards from Brazil
Marcelo



"Keith" escreveu:
 
K

Keith

Is there any way to disable the undo feature or change it's settings do you
know? Will upgrading to 2GB help do you think?

macropod said:
Hi Keith,

The problem here may be due to the fact that Excel is trying to store the
data refresh and pivot table updates in its undo buffer, just in case you
change your mind. When you save and close the file, that flushes the undo
buffer an lets Excel start over again.

Cheers

--
macropod
[MVP - Microsoft Word]


Keith said:
I use Excel a lot to access in Informix database through ODBC. I currently
have a file this is causing problems.

The file is 179Mb in size and has one sheet containing the data from
Informix which has about 14500 rows. On top of that there are 9 - 10 pivot
tables all relating to this data.

Once a month I refresh the data then go through each pivot table and update
it. My problem is that after updating the data and 3 or 4 tables the system
hangs. The CPU has 51% usage by Excel and 49% free.

If on the other hand I do 2 or 3 tables then save the spreadsheet and close
Excel then re-open the file it carries on fine.

Why is this happening? I thought that it was due to lack of RAM but I have
1GB. Is there a setting I need to change or something?
Click to expand...
Click to expand...
 
M

macropod

Hi Keith,

About the only thing that will help here is a faster CPU. AFAIK, you can't
change the number of undo levels available, but you can clear the undo
buffer by saving the workbook.

Cheers

--
macropod
[MVP - Microsoft Word]


Keith said:
Is there any way to disable the undo feature or change it's settings do you
know? Will upgrading to 2GB help do you think?

macropod said:
Hi Keith,

The problem here may be due to the fact that Excel is trying to store the
data refresh and pivot table updates in its undo buffer, just in case you
change your mind. When you save and close the file, that flushes the undo
buffer an lets Excel start over again.

Cheers

--
macropod
[MVP - Microsoft Word]


Keith said:
I use Excel a lot to access in Informix database through ODBC. I currently
have a file this is causing problems.

The file is 179Mb in size and has one sheet containing the data from
Informix which has about 14500 rows. On top of that there are 9 - 10 pivot
tables all relating to this data.

Once a month I refresh the data then go through each pivot table and update
it. My problem is that after updating the data and 3 or 4 tables the system
hangs. The CPU has 51% usage by Excel and 49% free.

If on the other hand I do 2 or 3 tables then save the spreadsheet and close
Excel then re-open the file it carries on fine.

Why is this happening? I thought that it was due to lack of RAM but I have
1GB. Is there a setting I need to change or something?
Click to expand...
Click to expand...
Click to expand...
 
C

Chip Pearson

AFAIK, you can't change the number of undo levels available,

Yes, you can. See http://support.microsoft.com/?kbid=211922


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


macropod said:
Hi Keith,

About the only thing that will help here is a faster CPU.
AFAIK, you can't
change the number of undo levels available, but you can clear
the undo
buffer by saving the workbook.

Cheers

--
macropod
[MVP - Microsoft Word]


Keith said:
Is there any way to disable the undo feature or change it's
settings do you
know? Will upgrading to 2GB help do you think?

macropod said:
Hi Keith,

The problem here may be due to the fact that Excel is trying
to store the
data refresh and pivot table updates in its undo buffer,
just in case you
change your mind. When you save and close the file, that
flushes the undo
buffer an lets Excel start over again.

Cheers

--
macropod
[MVP - Microsoft Word]


I use Excel a lot to access in Informix database through
ODBC. I currently
have a file this is causing problems.

The file is 179Mb in size and has one sheet containing the
data from
Informix which has about 14500 rows. On top of that there
are 9 - 10
pivot
tables all relating to this data.

Once a month I refresh the data then go through each pivot
table and
update
it. My problem is that after updating the data and 3 or 4
tables the
system
hangs. The CPU has 51% usage by Excel and 49% free.

If on the other hand I do 2 or 3 tables then save the
spreadsheet and
close
Excel then re-open the file it carries on fine.

Why is this happening? I thought that it was due to lack
of RAM but I have
1GB. Is there a setting I need to change or something?
Click to expand...
Click to expand...
Click to expand...
 
M

macropod

I stand corrected. Thanks, Chip.

--
macropod
[MVP - Microsoft Word]


Chip Pearson said:
AFAIK, you can't change the number of undo levels available,
Click to expand...

Yes, you can. See http://support.microsoft.com/?kbid=211922


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


macropod said:
Hi Keith,

About the only thing that will help here is a faster CPU.
AFAIK, you can't
change the number of undo levels available, but you can clear
the undo
buffer by saving the workbook.

Cheers

--
macropod
[MVP - Microsoft Word]


Keith said:
Is there any way to disable the undo feature or change it's
settings do you
know? Will upgrading to 2GB help do you think?

:

Hi Keith,

The problem here may be due to the fact that Excel is trying
to store the
data refresh and pivot table updates in its undo buffer,
just in case you
change your mind. When you save and close the file, that
flushes the undo
buffer an lets Excel start over again.

Cheers

--
macropod
[MVP - Microsoft Word]


I use Excel a lot to access in Informix database through
ODBC. I currently
have a file this is causing problems.

The file is 179Mb in size and has one sheet containing the
data from
Informix which has about 14500 rows. On top of that there
are 9 - 10
pivot
tables all relating to this data.

Once a month I refresh the data then go through each pivot
table and
update
it. My problem is that after updating the data and 3 or 4
tables the
system
hangs. The CPU has 51% usage by Excel and 49% free.

If on the other hand I do 2 or 3 tables then save the
spreadsheet and
close
Excel then re-open the file it carries on fine.

Why is this happening? I thought that it was due to lack
of RAM but I have
1GB. Is there a setting I need to change or something?
Click to expand...
Click to expand...
Click to expand...
 
Top