Microsoft Office Tutorials and References
In Depth Information
Optimizing for Performance
CP Operations such as loading files and querying external data consume I/O and network resources, and
storing the session state requires memory. Operations such as calculating the workbook use the CPU on
the ECS. The EWA and API on the WFE are not very CPU-intensive.
For many workbooks, the calculation is relatively fast (less than 1 second). Other calculations might
require seconds, minutes, and — in some extreme scenarios — even hours. The following sections
discuss ways of achieving your performance goals with both low-end and high-end workbooks.
The low-end workbooks are the majority. They might have tens or hundreds of formulas, and they
calculate very fast when run by themselves on a machine. But you should still optimize these workbooks,
because the server runs many of them in parallel. Here are some tips:
Try not to use volatile formulas and UDFs in your workbooks (these are functions that return a
different value on every calculation). The Volatile Function Cache Lifetime administrator setting
determines how current your volatile formula will be. If you allow formulas to be less current,
your performance will improve because the ECS will use cached results of previous calculations
of these formulas.
Remember that UDFs may consume CPU. Optimize your UDFs and the way they are called
from the workbook (for example, you might use some caching in the UDF). Test the UDFs and
install only those that are efficient.
You can set the Workbook Calculation Mode setting to Manual to prevent some trusted
locations from performing automatic calculations. This gives you control over which workbook
authors can use the CPU, and enables you to focus your efforts on optimizing the workbooks
that are allowed to perform calculations.
Make sure your workbook authors optimize the workbooks they create. Start with the
workbooks that are used the most, because improving them will have the largest impact on your
If the CPU is underutilized (the CPU is not close to 100 percent utilization, and yet the ECS has
reached its maximum throughput), try increasing the number of available threads.
You can use Excel Services to offload heavy calculations to the server. Relatively few workbooks with
heavy calculations are calculated once or iteratively, usually via the API. A classic example is a Monte
Carlo simulation (which tests the effects of various inputs over a model). Here are a few
recommendations for best performance:
Run high-end workbooks on dedicated hardware. You can do this by using a separate farm or
by running it during the night when the normal end-user activity is at a minimum. Having
dedicated hardware allows you to configure your server settings to optimize for this scenario. In
addition, it provides predictable performance, because no other noise is running on the server.
If your workbooks perform little to no external data queries, consider reducing the number of
threads on the ECS to between 1 and 4 per CPU. This configuration will result in less context
switching and better use of the CPU and memory.