Microsoft Office Tutorials and References
In Depth Information
Optimizing for Performance
As you learned earlier in this chapter, a session might share its state with other sessions, as long as they
contain the same workbook information. When the session has a shared state, its size is very small. The
size of a session that is not shared depends on the size of the workbook. To reduce the size of the session,
use smaller workbooks. In addition, you can do the following to share as much state as possible between
Use the same credentials for all users when accessing external data. This is appropriate for most
scenarios, except when the query returns personal information that depends on the user
opening the workbook.
Use the External Data Cache Lifetime settings to determine how current you need your data to
be. For example, if you know that your back-end data source is updated every 24 hours, there
is no point in refreshing your query every 5 minutes. (For more details on optimizing external
data, see Chapter 5.)
Minimize the use of workbooks with volatile formulas and UDFs. Volatile functions return a
different value on every calculation. You can use the Volatile Function Cache Lifetime
administrator setting to determine how current your volatile formulas will be. The less current they are,
the better the performance will be (at the expense of having them recalculate every time).
Using UDFs that return personal information prohibits workbooks from being shared. Call
these UDFs only where this is absolutely necessary.
Performing operations (such as setting the value of parameters, drilling, filtering, and sorting)
results in the workbook not being shared between sessions. As the designer of a web page, you
can configure the EWA to not allow these operations.
Cache Size of Unused Items
You learned about the unused items cached earlier in this chapter. You can configure the size of the
cache and how long items are kept there with the Maximum Private Bytes, Memory Cache Threshold,
and Maximum Unused Object Age settings.
Increasing the number of items that can be cached so that future requests will reuse these cached items
instead of recalculating them enhances the performance of the server. The downside of having more items
cached is that it requires more memory to be available to the ECS process.
As with almost any process, the performance of the ECS is improved significantly if there is enough
physical memory. The recommended configuration is a 64-bit machine, which allows for scaling up in
terms of memory.
The ECS is designed to use memory for improved performance, and you should take that into
consideration when tuning up your system. Depending on your specific needs, try to create a balance between
reducing the memory consumption (following the tips presented earlier) and increasing the available
As part of this equation, you should also take into consideration what other processes are running on the
ECS machine and their memory requirements. When there are several SSPs running, keep in mind that
each SSP has its own ECS process with its memory considerations and separate settings.