Implementing Static Cache to Improve Performance

Objectives

After completing this lesson, you will be able to:

  • Improve calculation view performance with static cache

Caching View Results

Complex calculation views place a heavy load on the system. To reduce this, it's possible to cache the results of a calculation view in order to speed up the time needed to get the results when executing a query against the view. This feature is useful when you have complex scenarios or large amounts of data so that the results don't have to be recalculated each time the query is executed. The benefits aren't just the reduced time for the results to appear for the user of the query, but it also means that CPU and memory consumption is reduced, leading to better performance for other tasks.

If the cache can't be used, due to a query requesting a different data set than what is held in cache, a query doesn't fail. It means that the query will need complete processing.

Caching shouldn't be applied to raw data but to data that has been highly processed and reduced through aggregations and filtering. In practice, this means applying caching to the top-most nodes of a calculation view.

Cache can only be used for calculation views that don't check for analytic privileges. This means analytic privileges should be defined on the top-most view only, in a calculation view stack. The optimal design would be to define the cache setting at the highest calculation view possible in the stack, but not at the very top where analytic privileges are checked. This means that the user privileges are always checked against the cached data, if the cache is useable.

It's possible to fine-tune the calculation view cache using column and filter settings.

Firstly, calculation view caching can be defined at the column level. This means queries that use only a sub-set of the cached columns can be served by the cache. It's recommended to cache only the columns that are frequently requested in order to reduce memory consumption and speed up cache refresh.

Caution
If you don't specify any columns in the cache settings, then all columns are cached.

The cache size can be further reduced by defining filters in the cache settings. Queries that use either exactly the cache filters, or a subset of the filters, are served by the cache. Cache is only used if the query filter matches exactly the filter that is defined for the cache, or reduces the data further.

It's possible to define a retention period of the cache so that the cached data expires. The value entered is in minutes.

Note
It's currently not possible to specify that cache should be invalidated if new data is loaded to the underlying tables. This feature is expected to come later. For now, we just have a time-based expiry (in minutes).

In order to use the calculation view static cache, there are some important prerequisites that must be met.

Firstly, the basic setting Enable Cache must be set in the calculation view to be cached.

Then, the query must be able to be fully-unfolded. This means that the entire query must be fully translatable into a plain SQL statement and not require the calculation of interim results. You can use the Explain Plan feature of the SQL Analyzer to check if unfolding can occur.

There must be no calculations that depend on a specific level of calculated granularity as the cache is stored at the level of granularity requested by the first query. This may not align to the aggregation level required by subsequent queries and could cause incorrect results.

Even with these prerequisites met, the cache feature is disabled by default and is only considered when explicitly invoked by using one of the following RESULT_CACHE hints:

If the prerequisites for cache usage aren't met, it might still be possible to force the cache to be used. You can use the setting Force so that the cache is used. However, you should test extensively to ensure that correct results are returned.

Log in to track your progress & complete quizzes