Microsoft Office Tutorials and References
In Depth Information
The following table shows an example of data caching and sharing.
User A refreshes a connection.
The connection is not in the cache. ECS
performs the query and saves the result in the
cache. The cache will expire at 12:05.
User A refreshes the same
The cache is not expired. The ECS reuses the
connection the cache.
User B (who has the same
credentials as user A) refreshes
The credentials match. The ECS reuses the
same connection from the cache.
User C (who has different
credentials) refreshes the connection.
The ECS performs the query with the new
credentials and caches the new results. These
cached results will expire at 12:08.
User A refreshes the connection.
The cache entry for user A is expired. The ECS
performs the query again.
Cache Lifetime Settings
Two settings define the external data query cache expiration. The Automatic Refresh Data Cache Lifetime
setting is used when a workbook is opened with refresh on open and for periodic refresh. The Manual
Refresh Data Cache Lifetime setting is used when a connection is explicitly refreshed.
The default value for both settings is 5 minutes. You can change this setting from the administration
pages for each trusted location. Various databases have different update schedules. Some may update
once a week or once a day; others may have new data every hour, every minute, or even close to real
time. In addition, even if the database updates frequently, each workbook will need data refreshed on
a different schedule. For example, a workbook could use a data query to create an historic quarterly
report, so it won’t need up-to-date data. Other workbooks could be used to perform operational
decisions based on the most current information.
When you’re setting these values, balance the performance of the server with the user’s need for up-to-date
data. In many cases, there is no conflict, because workbooks don’t need the latest information or the
database itself doesn’t update that often. The default values for these settings are relatively low, and, in most
cases, you should increase them to achieve better performance.
In addition, the workbook author has an impact on the performance. If the workbook refreshes on open or
has periodic refresh, it will potentially perform more queries than a workbook with no automatic refresh.
The actual periodic refresh period will be the maximum between the one defined in the workbook and the
Automatic Refresh Data Cache Lifetime setting. For example, if the periodic refresh defined in the
workbook is 10 minutes but the cache lifetime setting is 1 hour, the workbook will refresh automatically only
once an hour.