Automation performance degradation in Excel 2007

[John Austin]

I have a VB6 app that creates about 100 populated Excel spreadsheets. I have
installed Office 2007 and the performance of the application has degraded
significantly.

On the same PC, extracting data from the same database, the creation process
takes 50 seconds with Office 2003, but 165 seconds with Excel 2007.

What is happening here? Why is Excel 2007 so slow? The application creates
several hundred of these spreadsheets each week and takes over an hour to
complete with Excel 2003. If Excel 2007 were used it would take all day!

 

[Jialiang Ge [MSFT]]

Hello John,

From your post, my understanding on this issue is: you wonder why the
automation of Excel 2007 looks slower than that of Excel 2003, and how to
improve its performance. If I'm off base, please feel free to let me know.

According to the MSDN article
http://msdn2.microsoft.com/en-us/library/aa730921.aspx (Improving
Performance in Excel 2007), the performance issue is caused by the
increased limits in Excel 2007. The Excel 2007 "Big Grid" increases the
maximum number of rows per worksheet from 65536 to over 1 million, and the
number of columns from 256 (IV) to 16384 (XFD) (See
http://msdn2.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_B
igGridIncreasedLimitsExcel). When we create large worksheets, it is easy to
build them in a way that causes them to calculate slowly. However, by using
a straightforward set of techniques, we can speed up most slow-calculating
worksheets by a factor of 10 or 100:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_M
akingWorkbooksCalculateFaster (Excel 2007 Performance Improvements)
http://msdn2.microsoft.com/en-us/library/aa730921.aspx#Office2007excelPerf_F
indingPrioritizingCalculationBottlenecks (Finding and Prioritizing
Calculation Bottlenecks)
http://msdn2.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_E
xcelPerformanceImprovements (Excel 2007 Performance Improvements)
http://msdn2.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_T
ipsOptimizingBottlenecks (Tips for Optimizing Bottlenecks)

For instance, we could utilize the new feature of Excel 2007:
multi-threaded calculation to improve the calculation speed, or temporarily
set the calculation mode as "Manual" so that the formulas are only
recalculated when you request it. You can also apply these techniques as
you design and create worksheets to ensure that they calculate quickly.

The Excel development team has been spending a lot of time tuning Excel
2007's calculation performance to make it as fast as possible. (See:
http://blogs.msdn.com/excel/archive/2006/02/28/540939.aspx Help us make
Excel 2007 faster¡­). Here are a few more posts about Excel performance
from Excel team blog:
http://blogs.msdn.com/excel/archive/2005/11/03/488822.aspx Multi-threaded
calculation in Excel, or "how calculation can become much faster in Excel
12"
http://blogs.msdn.com/excel/archive/2006/10/23/excel-performance-new-whitepa
per-available.aspx Excel Performance - New Whitepaper Available
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx Some other
numbers ...

Please try the above techniques according to your specific situation, and
let me know if they can help you improve the performance. For any other
concerns or questions, please feel free to let me know.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Austin]

I will certainly review the articles, however, automatic calculation is
already turned off and is only turned on at the end just before saving the
file. The problem seems to be the actual insertion of the data via OLE
automation. I will look at the articles however and get baqck to you.

 

[John Austin]

Hello Jialiang,

I have read the article that you mentioned. The problem that I have is not
with the calculation of the workbook, it is the time taken to insert data in
the workbook (with automatic calculation turned off) from a VB application
via OLE automation. This is the process that will take over 5 hours instead
of 1.5 hours.

 

[Jim Rech]

Significantly slower performance with Excel 2007 is a frequent lament here.
I had not seen Excel 2007 do anything in less than twice the time it took
Excel 2003, not that I've spent my life doing time trials.

I decided to see how automation via a simple VBS script compared. To my
surprise 2007 was only about 50% slower. Here's the script (save in a text
file with a VBS extension):

Dim XL
set XL = GetObject(,"Excel.Application")
xl.screenupdating=false
st = timer
for counter = 1 to 5000
xl.cells(counter,1).value = counter
next
msgbox timer-st
xl.screenupdating=true

You have to have one instance of Excel open (e.g., either 03 or 07), with a
workbook present when you run this. This isn't great performance but it's
way quicker than you're seeing. One caveat - I'm using Excel 2007 with SP1.
No idea if the patch helped.

 

[Jialiang Ge [MSFT]]

Hello John,

Would you let me know if the excel file contains some charts created by the
input data? Is there any formula column? Office 2007 SP1 has fixed some
performance issues when a workbook contains some charts created by a large
amount of data in a worksheet. Therefore, my suggestion is to install the
Office 2007 SP1:
http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-
A997-25DA01F388F5&displaylang=en and see if it has some improvements in
performance of your automation client.

Some members from Excel product team told me that they are still doing
their best to improve the overall performance of Excel 2007. It is slower
than previous version mainly because of its new "big grid" and features.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Austin]

Hello Jialiang,

No charts! I installed SP1 this morning and there was a small improvement on
my Xp test machine:
Excel 2003 50 seconds
Excel 2007 165 seconds
Excel 2007 SP1 126 seconds
So even with SP1, 2007 takes 2.5 longer to create the spreadsheets.

 

[Jim Rech]

Big John-

You probably missed my post. What do you make of it? Did you get different
relative results? We both use automation to use entries in cells right?

--
Jim
| Hello Jialiang,
|
| No charts! I installed SP1 this morning and there was a small improvement
on
| my Xp test machine:
| Excel 2003 50 seconds
| Excel 2007 165 seconds
| Excel 2007 SP1 126 seconds
| So even with SP1, 2007 takes 2.5 longer to create the spreadsheets.
| --
| John Austin
|
|
| "Jialiang Ge [MSFT]" wrote:
|
| > Hello John,
| >
| > Would you let me know if the excel file contains some charts created by
the
| > input data? Is there any formula column? Office 2007 SP1 has fixed some
| > performance issues when a workbook contains some charts created by a
large
| > amount of data in a worksheet. Therefore, my suggestion is to install
the
| > Office 2007 SP1:
| >
http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-
| > A997-25DA01F388F5&displaylang=en and see if it has some improvements in
| > performance of your automation client.
| >
| > Some members from Excel product team told me that they are still doing
| > their best to improve the overall performance of Excel 2007. It is
slower
| > than previous version mainly because of its new "big grid" and features.
| >
| > Regards,
| > Jialiang Ge ([email protected], remove 'online.')
| > Microsoft Online Community Support
| >
| > =================================================
| > When responding to posts, please "Reply to Group" via your newsreader
| > so that others may learn and benefit from your issue.
| > =================================================
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >

 

[John Austin]

Hi Jim,

The results are interesting. My problem is that I havn't got 2 PCs the same
- I did my tests by uninstalling Excel 2007, which is tedious. I have a
suprising situation where my laptop/vista/Excel 2007/SQL Server 2005 is the
same speed as a PC/Xp/Excel 2003/SQL Server 2000. Each of the 100 workbooks I
produce have about 25 worksheets each with 80 columns by 232 rows. They also
use macros to calculate in places so it is difficult to know exactly where
the problem lies, if indeed it is not a general problem throughout Excel
2007. When I get some time I will do some tests like yours and let you know
the results.

Regards

 

[John Austin]

Dear Jialiang,

I now have another problem. When I first tried my application with
Vista/Excel 2007, I had terrible performance with the first worksheet in the
workbook - it was taking several minutes to produce instead of maybe 15
seconds. The problem then disappeared and the system has been running well.
Today the original problem has re-appeared (following a reboot) and I am back
to taking several minutes to create the sheet. I really need to resolve this
issue as I need to demonstrate the system to prospective customers.

Best regards,

 

[Jialiang Ge [MSFT]]

Hello John,

it was taking several minutes to produce instead of maybe 15
seconds. The problem then disappeared and the system has
been running well.

According to the description, can I understand that the low performance
issue *ONLY* occurs at the first run of the application after a system
reboot? Is that to say your automation client can always run in an
acceptable speed after this first run? If you close the application, ensure
that excel.exe process does not exist in your Windows Task Manager, then
start the application again, does it run slowly? Do you have office 2007
SP1 installed on the Windows Vista system?

In my opinion, there are four possible reasons that make your application
slow:
1. It is slow when create the Excel application object:
When the application is run for the first time, it needs to create the
Excel application object: CreateObject("Excel.Application"). This process
usually may take a few seconds, but not several minutes. The application
should be invisible after it is created. Did you make it visible?
2. It is slow when open an Excel workbook
At the first run of the application, I wonder whether you created a new
workbook or opened an existing one? If you opened an existing workbook, did
the workbook contain a lot of data?
3. It is slow when retrieving data from data source
According to your first message, you extracted data from a database. Is it
possible that the database was restarted, and it was slow to retrieve data
after the restart?
4. It is slow when insert data into the worksheet.
According to your description, there is no formula column, no chart, no
macro existing in the worksheet, right? According to the responses from
Excel product team, most of the low performance when updating data in
worksheet is because the time spent drawing the statusbar and repainting
the screen has gotten slower. Here are a few more suggestions that can
improve the performance even if the visibility of the Excel application is
false:
a). Setting Application.ScreenUpdating to FALSE will speed
things up dramatically.
b). Turning off the Page Breaks makes a dramatic improvement
if they are on (have to turn off each time they get turned back on).
http://support.microsoft.com/default.aspx?scid=kb;EN-US;199505. Usually, if
you format the workbook and utilize the PageSetup object, the Page Breaks
will be turned on and slow the performance. Have you ever used PageSetup
object or formatted the workbook in your application?
c). creating an array and enter it into the cells all at one
time. This would be much faster than entering data in one piece at a time.

In order to determine which reason (1,2,3 or 4) slows your application, my
suggestion is that we could pop out a message when each phase is finished
in your application. Or we could calculate the time used for each phase by
timeEnd - timeStart, where timeEnd is the DateTime.Now when the phase ends,
and timeStart is the time when the phase starts. Please do the test and let
me know which phase makes your application slow. Would you send some sample
codes to my mailbox so that I can have a clearer picture of the procedure
that you import data into worksheets.

Last but not least, I hope to let you know that troubleshooting such issues
in newsgroup might not be the best channel due to the low efficiency when
we exchange the information. However, I will try my best to help you. If
you feel the issue is critical, I suggest that you could contact our
Product Support Services. The problem can be quickly and effectively
resolved with the direct assistance from a Microsoft Support Professional.
You can contact Microsoft Product Support directly to discuss additional
support options you may have available, by contacting us at 1-(800)936-5800
or by choosing one of the options listed at
http://support.microsoft.com/common/international.aspx?rdpath=fh;en-us;cntac
tms.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Austin]

Sorry, Jialiang, I should have been clearer about what happened.

The application has been working fine for several years with Excel 2000 and
2003.

Normally the CPU runs at about 50% (dual core). I tried the application for
the first time with Office 2007 on a new laptop and had the speed problem
from the start - -several minutes for the first sheet, then about 2 seconds
each for the rest. The CPU runs at 10% or less for the first sheet. The
problem lasted for two days and then magically disappeared - performance was
good and CPU was back to 50%. At some point SP2 was installed which may have
something to do with it. After a week the program suddenly started going
really slowly again for the first sheet, with low CPU usage. The problem
persists through reboots etc and is not anything to do with multiple copies
of Excel. The Excel application is never visible and automatic calculation is
turned off.

 

[Jialiang Ge [MSFT]]

Hello John,

According to your description, my understanding is:
1. The Excel automation client was fast with Excel 2000 or 2003
2. The Excel automation client became slow after the installation of Excel
2007
3. The Excel automation client suddenly turn fast possibly due to the
installation of Excel 2007 SP2
4. The Excel automation client became slow again after one week. But we do
not know the reason. Reboot does not help, either.

Is it correct?

I am still wondering which reason (1,2,3 or 4 as is mentioned in my last
reply) slows your application. Would you help me do the test in your side,
and let me know the result? Knowing the phase that affects the performance
can help us narrow the focus. Have you ever tried the suggestions in reason
3/(a)(b)(c) of my last reply? What is the excel file format (xls or xlsx)
when you imported the data? You may also try to manually insert data into
an xlsx file in your Excel 2007 and see if it performs in an expected speed.

I did a test with an Excel 2007 automation client. Its performance is
acceptable in my Windows Vista 32bit Enterprise, Excel 2007 Enterprise,
Intel P4 3.0GHz, 2046MB RAM system. I understand that we didn't make
progress on this issue so far. It's mainly because the issue is not
reproducible and seems random. Would you send a sample project to my
mailbox ([email protected])? I will test the project in my side and see
if the issue is persistent.

Happy New Year!
Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Austin]

Hi Jialiang,

The issue is complex, but I will do some tests and get back to you.

 

[eimund]

Hi Jialiang,

The issue is complex, but I will do some tests and get back to you.
--
John Austin











- Vis sitert tekst -

Hi John!

Have you found any solution to your problem?
As I read this thread above, I recognize much of what you are writing.
I have a project myself that uses VBA macros to extract data from a
SQL Server 2005 database, I'm using Excel 2007 (Windows XP) and when I
try to copy the recordset to a cell in a worksheet, it takes a huge
amount of time, if it finishes at all! Often I need to just end the
excel application and start over. It works ok in excel 2003, but not
in the 2007 edition.

Regards,
Eimund

 

[John Austin]

Hi Eimund,

The project has been postponed and I have had a few other problems to sort
out, but no - I still get strange results. I will post any info. that comes
to light.

Regards,