Back
Excel

Performance Improvements in Excel 2010

Thanks to Chad Rothschiller for putting together these posts on performance.

In this release of Excel we spent dedicated research and development resources on improving performance. In addition to the usual optimizations and tuning efforts we make post “code completeâ€, we spent time during the development milestones researching, designing, and implementing performance features beyond algorithmic tuning. For this release, these are the areas where we focused our performance improvement efforts:

  • Customer Feedback
  • Large Data Set scenarios
  • Calculation
  • Multi-Core Opportunities

As you’ve heard us say before, the Excel team takes performance very seriously. We know that our customers build intensive solutions that help businesses make key decisions, and performance of Excel and those solutions is paramount. In this 2-part series I’m going to talk about the challenges and opportunities we embraced when planning our work for Excel 2010 performance.

In this post I’m going to talk about the first one: what we’ve been doing to address customer feedback around the performance of Excel 2007.

Improving Performance Based on Customer Feedback

Office 2007 was a big release for us, introducing some major architectural investments that resulted in significant new capabilities. We scaled up the size of the grid (now 1,048,576 rows by 16,384 columns), increased a bunch of limits across the product, introduced a shiny new graphics platform and invested in upgrading our charting abilities, implemented a new user interface, greatly expanded the concept and usage of styles, and shipped Excel Services, which shares source code with Excel client in order to ensure fidelity. We knew that performance of the new architecture was very important, so we spent a lot of time working on performance in Excel 2007. When we released Excel 2007 we had addressed the key performance issues we found during development, but we also knew that as customers started using Excel 2007, we would get additional feedback around performance. And we did.

A major initiative for us this release was to seek out that feedback, get engaged with the issues, and work to tackle them. Our goal was to seriously and significantly improve performance, but we knew that was going to be hard, given the big architectural changes that came with the Excel 2007 release. However, we dug in hard, got to work, and made good progress against our goals.

We gathered up as many customer examples as we could get our hands on (you might recall a blog post I made in January of this year to that effect) to identify the problems that people were seeing. I also searched the web for people making comments about Excel 2007 performance and sought out their examples and feedback. Thanks again to all of you who provided and continue to provide us with your workbooks. All of your files have been collected, run, analyzed, re-run (several times over) helping us to prioritize and fix the right set of problems.

In the “customer feedback†category, here are the key areas where we made performance investments for Excel 2010:

Printer & Page Layout View

Page Layout View relies on printer-specific information and calculates a lot more layout information in order to render the view as the printer would. Unfortunately, Excel 2007 spent lots of time talking to the printer, re-talking to the printer, and calculating these metrics. As a result, basic tasks like entering data in the sheet could potentially be noticeably slower.

If the default printer happened to be offline, Excel 2007 ended up waiting for a very long printer timeout before becoming responsive again.

To address these issues, Excel 2010 caches the printer settings and we’ve further optimized the render calculations. We’ve also made connecting to the printer cancelable so that you as the user remain in control.

Shapes

We saw a good number of performance issues with Excel 2007 related to having lots of shapes on a worksheet (often they’re hidden shapes, the result of copy/pasting from a web page). The Office graphics team has taken a good look at those scenarios and done a lot of work to speed up performance in this area.

Additionally we took a look at Shape object model “*.select†performance and made good improvements there. The chart below shows how long it takes Excel 2007 vs. a beta version of Excel 2010 to run the following code, updating 300 text boxes with the text “chad†(call me a narcissist):

For i = 1 To 300
   ActiveSheet.Shapes(i).Select
   Selection.Characters.Text = “chad”
Next i

image

As you can see, we’re seeing huge gains in this area, compared to Excel 2007.

Charting

Ben Rampson outlines the significant charting performance improvements in a recent Excel blog post.

VBA Solutions

Another area of customer feedback was around performance of Excel 2007 when driven by VBA object model. Some of the ways that the OM was driving Excel exposed performance issues that simply weren’t noticeable when just booting up and interacting with Excel. For example, typing values in a cell felt responsive and fast. But running VBA code to update 100K cells with new values (one at a time) showed that Excel 2007 had significantly slowed down.

After receiving a good number of examples from you folks, we were able to run, analyze, fix, re-run and continue iterative performance fixing in this area. Here’s a summary of our gains:

image

This graphic shows that nearly 70% of the workbooks in our VBA solutions test suite (the workbooks you all sent us) will see significant performance gains when run using Excel 2010, with about 30% of those solutions at least cutting the Excel 2007 times in half, if not more (some of those solutions clock in around 5% of the Excel 2007 time).

Closing Excel

Another, slightly smaller performance issue with Excel 2007 is the amount of time it takes to close down and free memory up to work with other programs, especially closing down after working with really large workbooks. With Excel 2010 we implemented a fast shutdown mechanism, and I hope you enjoy the snappiness.

In the next post I’ll talk about performance improvements we made around Large Data Set scenarios, Calculation, and Multi-Core investments.

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags