Speed problem with Excel 2007

[Martien]

I am currently evaluating Office 2007 (Trial mode of the release
version of Office Standard) for application in our office. We are still
using Office 97. Office 2007 does of course have very many improvements
which starts to convince me that an upgrade is (finally) becoming very
interesting.

However, we use Excel a lot and build our own applications as well as
for customers. Testing these applications I found that Excel 2007
responded in many cases very slow, especially with charts.

So I made a very simple test case which you can try out.
Column A containes numbers from 1 to 1000 from row 1 to 1000
Column B to U contains data values (so 20 columns)
I select column A to U, row 1 to 1000 and insert a chart with XY data
(scatter chart), using no data markers and straight lines between the
data points (I did apply the default line style etc, so not one of the
more fancy styles). I got the chart quickly drawn on my worksheet, no
problem.
Then, when I move the chart (which contains now 20 curves of 1000
points), it takes 10 seconds to redraw the chart on the worksheet. This
occurs at every move of the chart.

Of course I tested the same with Excel 97, moving the same chart around
is instantanous and I cannot measure the time, meaning it is well below
one second, so for sure more than a factor 10 faster.

This is just a simple example, but it does represent the general
experience I had with testing our applications. In fact, it makes
migration to Excel 2007 impossible for us. I am afraid that all the
increased productivity MS claims for Office 2007, will get lost by our
employees waiting for Excel to return with the results.

The increased timing seems to be related to graphic issues. I don't
think it is a memory problem. Excel 2007 used app. 45 MB from my memory
when doing the experiment. The test machine was a laptop with 2.2 GHz
processor and 512 MB memory. (I also tested Excel 2007 on a brand new
clean machine with WinXP and only office2007 installed but also there
our applications behave very slow).

Is there a setting in Excel which I may have overlooked which could
maybe simplify my charts or colour schemes, or whatever it is which
takes up all my CPU resources ?

 

[orbii]

hi, hopes this helps.

by following your instructions, i applied random to all those 1000x20 cells,
here's what i found out to make it work faster. i have 4g ram, running on a
dual cpu.

first, turn off enable live preview under popular options in excel, turn off
show chart element names on hover, also turn off show data points values on
hover. that should do the trick on lowering the overhead, it became almost
instant for me.

but who in the world would make a chart that big? perhaps 100x20 i can
understand but 1000x20, that's a bit too much for a graph. might also want
to think of adding more ram to those machines you got. 2g of ram is a joke,
so come on... i'm sure 1 hour worth of work can pay for that juicy stake.

and don't go adding on 2000kilo to a elevator that's already carrying
3000kilo

aloha, orbii

 

[Martien]

Dear Orbii

Many thanks for your help. I have tried out your suggestions (using the
same machine as before with 512 Mb and 2.2 GHz processor). Switching
off live preview does reduce the time to move the chart from app. 10
seconds to app. 5 seconds, so this is a big change. However, switching
off "show chart elements on hover" and "show data point values on
hover" did not give any noticeable improvement. In fact, since the
latter option was also already present in my Excel 97 baseline test, I
should in fact not switch it off (to keep the comparison fair).

I tested the same spreadsheet also on a "clean" machine with XP SP2 and
only Office 2007 on it with again 512 Mb but now a 3 GHz processor.
Here it took about 4 seconds to redraw the chart after moving it.
Switching off live preview did not give a measureable difference.

So I am still very far off my original performance with Excel 97. You
mentioned that my case may be somewhat overdone, as I am drawing curves
with 1000 data points. Just to explain, one of our applications is for
a laboratory set-up where we collect large amounts of data and evaluate
the data with Excel. WIth charts we can evaluate if the measurement
data contains e.g. irregularities. If we would display only 100 points
over a certain time zone we could miss very easily something. My
criterium is that it is only nonsense to draw more data points than my
display would generate, therefore 1000 data points is to us very
realistic and probably even low with modern screens. Moreover, for many
other graphicly oriented program (such as most games) drawing and
updating only 20 x 1000 data points is of course a piece of a cake.

I don't think that the point should be a discussion of my test case,
but the problem which I have in accepting that if we used such curves
with Excel 97 very satisfactorally over the last 10 years, why we now
should accept a performance degradation of more than a factor 5 (with
live preview off).

Maybe the key point is memory as you suggest. I agree that 512 MB is a
little scarse at the moment (though above the recommended memory for
Office 2007 according MS). However, I looked at memory usage during my
test cases and in all cases there was still pleanty of physical memory
available during my tests (at least following the task manager), so I
am wondering what is happening here. I am not afraid to spend some
money on our machines for extra memory. I just want to understand if
that really would solve the problem.

Do you know of other "switches" in Excel 2007 which may further reduce
the overhead ?

Thanks again for your reply,

 

[orbii]

hmm, i've tested quite a few thing myself and couldn't get it to go any
faster. and yes i do see how you'd need 1000x20 or even bigger. i'd
imagine how complicated it would be to list n cata the human dna in excel.

if the move of 97 to 07 is great enough of an impact $$$ wize for MS, i'm
sure you can call a microsoft rep and ask if they can put a hand in it too.
when i was doing access db for a major company in seattle, we had to switch
about a little over 500 machines to the new access. had tons of problems w/
the new one, so we ended up submitting bug reports everyday and they were
fixing it as we finds it.

weird thing is that i know for a fact that the display engine for all the
office content area arn't written by microsoft when the funny thing is
directx is owned by them... how odd. that makes me think a $50 game today
could beat excel pound for pound.

oh just thought of something that you might want to try, instade of having
the object itself updates whenever it moves or being moved or when another
overlay windows moves over, it shoudn't update until a function is called.
perhaps that will avoid the lag when moving charts around. not sure how the
code would work nor am i exp enough w/ the new excel to even try to figure
it out. but u've got me going on this... good problem, i'll keep on
hunting.

aloha, orbii

 

[Chris]

I have a similar problem, again using a large data set, but not only do i find the rendering to be slow - i can put up with that since i dont actually ened to move the chart around. i want to output the generated charts to send to clients - which i used to do by printing them to pdf. under excel XP it took abtou 5-10 seconds to print 10 charts. under '07 .. well.. it has never gotten round to finishing it.. i killed it once i got bored waiting for page 1! i downloaded the office "save to pdf plugin" and while this does work - it still takes about 2 minutes per page. and this is on a 2.6ghz core 2 duo, with 2 gb ram. it makes me worry there is something more fundamentally inefficient with how excel is now drawing its charts.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Chris]

I have a similar problem, again using a large data set, but not only do i find the rendering to be slow - i can put up with that since i dont actually ened to move the chart around. i want to output the generated charts to send to clients - which i used to do by printing them to pdf. under excel XP it took abtou 5-10 seconds to print 10 charts. under '07 .. well.. it has never gotten round to finishing it.. i killed it once i got bored waiting for page 1! i downloaded the office "save to pdf plugin" and while this does work - it still takes about 2 minutes per page. and this is on a 2.6ghz core 2 duo, with 2 gb ram. it makes me worry there is something more fundamentally inefficient with how excel is now drawing its charts.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[orbii]

wow, you're right, i just tested it too... this is pretty bad.

after 2 days in the meeting room, we (company i work for) decided not to
intro 2007 to our clients for many reasons. aside from the UI and extra
row/col. everyone have found 07 to be quite disappointing. sad to say i
wont' be developing much excel stuff and moving back to php. sigh....

aloha, orbii