Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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! -- John Austin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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/lib...007excelPerf_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/lib...007excelPerf_M akingWorkbooksCalculateFaster (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelPerf_F indingPrioritizingCalculationBottlenecks (Finding and Prioritizing Calculation Bottlenecks) http://msdn2.microsoft.com/en-us/lib...007excelperf_E xcelPerformanceImprovements (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelperf_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/...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/...03/488822.aspx Multi-threaded calculation in Excel, or "how calculation can become much faster in Excel 12" http://blogs.msdn.com/excel/archive/...ce-new-whitepa per-available.aspx Excel Performance - New Whitepaper Available http://blogs.msdn.com/excel/archive/...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 , 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/subscripti...ult.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/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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 "Jialiang Ge [MSFT]" wrote: 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/lib...007excelPerf_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/lib...007excelPerf_M akingWorkbooksCalculateFaster (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelPerf_F indingPrioritizingCalculationBottlenecks (Finding and Prioritizing Calculation Bottlenecks) http://msdn2.microsoft.com/en-us/lib...007excelperf_E xcelPerformanceImprovements (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelperf_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/...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/...03/488822.aspx Multi-threaded calculation in Excel, or "how calculation can become much faster in Excel 12" http://blogs.msdn.com/excel/archive/...ce-new-whitepa per-available.aspx Excel Performance - New Whitepaper Available http://blogs.msdn.com/excel/archive/...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 , 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/subscripti...ult.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/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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. -- John Austin "Jialiang Ge [MSFT]" wrote: 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/lib...007excelPerf_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/lib...007excelPerf_M akingWorkbooksCalculateFaster (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelPerf_F indingPrioritizingCalculationBottlenecks (Finding and Prioritizing Calculation Bottlenecks) http://msdn2.microsoft.com/en-us/lib...007excelperf_E xcelPerformanceImprovements (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelperf_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/...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/...03/488822.aspx Multi-threaded calculation in Excel, or "how calculation can become much faster in Excel 12" http://blogs.msdn.com/excel/archive/...ce-new-whitepa per-available.aspx Excel Performance - New Whitepaper Available http://blogs.msdn.com/excel/archive/...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 , 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/subscripti...ult.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/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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. -- Jim "John Austin" wrote in message ... 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. -- John Austin "Jialiang Ge [MSFT]" wrote: 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/lib...007excelPerf_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/lib...007excelPerf_M akingWorkbooksCalculateFaster (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelPerf_F indingPrioritizingCalculationBottlenecks (Finding and Prioritizing Calculation Bottlenecks) http://msdn2.microsoft.com/en-us/lib...007excelperf_E xcelPerformanceImprovements (Excel 2007 Performance Improvements) http://msdn2.microsoft.com/en-us/lib...007excelperf_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/...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/...03/488822.aspx Multi-threaded calculation in Excel, or "how calculation can become much faster in Excel 12" http://blogs.msdn.com/excel/archive/...ce-new-whitepa per-available.aspx Excel Performance - New Whitepaper Available http://blogs.msdn.com/excel/archive/...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 , 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/subscripti...ult.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/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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/d...594-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 , 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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/d...594-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 , 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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 "John Austin" wrote in message ... | 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/d...594-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 , 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. | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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 "Jim Rech" wrote: 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 "John Austin" wrote in message ... | 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/d...594-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 , 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. | | |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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, -- 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/d...594-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 , 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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...b;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/...fh;en-us;cntac tms. Regards, Jialiang Ge , 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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. -- John Austin "Jialiang Ge [MSFT]" wrote: 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...b;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/...fh;en-us;cntac tms. Regards, Jialiang Ge , 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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 )? I will test the project in my side and see if the issue is persistent. Happy New Year! Regards, Jialiang Ge , 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
Hi Jialiang,
The issue is complex, but I will do some tests and get back to you. -- John Austin "Jialiang Ge [MSFT]" wrote: 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 )? I will test the project in my side and see if the issue is persistent. Happy New Year! Regards, Jialiang Ge , 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
On 28 Des 2007, 00:25, John Austin wrote:
Hi Jialiang, The issue is complex, but I will do some tests and get back to you. -- John Austin "Jialiang Ge [MSFT]" wrote: Hello John, According to your description, my understanding is: 1. TheExcelautomation client was fast withExcel2000 or 2003 2. TheExcelautomation client became slow after the installation ofExcel 2007 3. TheExcelautomation client suddenly turn fast possibly due to the installation ofExcel2007SP2 4. TheExcelautomation 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 theexcelfile format (xls or xlsx) when you imported the data? You may also try to manually insert data into an xlsx file in yourExcel2007and see if it performs in an expected speed. I did a test with anExcel2007automation client. Its performance is acceptable in my Windows Vista 32bit Enterprise,Excel2007Enterprise, 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 )? I will test the project in my side and see if the issue is persistent. Happy New Year! Regards, Jialiang Ge , 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.- Skjul sitert tekst - - 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation performance degradation in Excel 2007
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, -- John Austin "eimund" wrote: On 28 Des 2007, 00:25, John Austin wrote: Hi Jialiang, The issue is complex, but I will do some tests and get back to you. -- John Austin "Jialiang Ge [MSFT]" wrote: Hello John, According to your description, my understanding is: 1. TheExcelautomation client was fast withExcel2000 or 2003 2. TheExcelautomation client became slow after the installation ofExcel 2007 3. TheExcelautomation client suddenly turn fast possibly due to the installation ofExcel2007SP2 4. TheExcelautomation 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 theexcelfile format (xls or xlsx) when you imported the data? You may also try to manually insert data into an xlsx file in yourExcel2007and see if it performs in an expected speed. I did a test with anExcel2007automation client. Its performance is acceptable in my Windows Vista 32bit Enterprise,Excel2007Enterprise, 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 )? I will test the project in my side and see if the issue is persistent. Happy New Year! Regards, Jialiang Ge , 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.- Skjul sitert tekst - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I improve the 2007 Excel performance. | Excel Discussion (Misc queries) | |||
Degradation of performance : upgrading from Excel 2000 to Excel 20 | Excel Programming | |||
Degradation of performance when upgrading from Ecel 2000 to Excel | Excel Discussion (Misc queries) | |||
Temp files causing performance degradation | Excel Discussion (Misc queries) | |||
Excel Automation - Chart Performance | Excel Programming |