Implementing Static Cache to Improve Performance

Objective

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.

Overview of the static cache principles. A first execution of Query A at 9:10AM will fetch the data from the database and store the result in the cache, taking a certain amount of time. A subsequent execution at 10:30AM will only have to read the data from the cache, thus taking much less time.

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.

Screen Capture of a Calculation View properties. In the Static Cache tab, the Enable Cache property has been selected. Three columns have been specified as to be cached. A Query selecting only cached columns will use the cache, a query selecting any other column will not.

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.
Screen Capture of a View properties. In the Static Cache tab, three columns are listed as being cached and a filter is defined on COLUMN3. Only queries using the cached columns and at least the exact filter as defined, will use the cache.

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.

Screen Capture of a View Properties. The Static Cache tab shows a value of 10 for the Retention Period property (the maximum acceptable age of cached data in minutes before a query triggers a cache refresh).

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).

Static Cache Prerequisites

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

  • Enable Cache is selected
  • Calculation View can be unfolded
  • No granularity tracking calculations that prevent cache-use (to avoid unexpected results)

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

Screen capture of a Calculation View properties. In the Static Cache tab, the Enable Cache property is selected.

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.

Static Cache Invocation

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:

  • Hint is added to your SQL Query:

    SELECT ...WITH HINT (RESULT_CACHE)

  • A database hint is used in the top-most view, which consumes to-be-cached view
  • Configuration parameter is set:

    indexserver.ini -> [result_cache] -> enabled

Screen capture of Calculation View properties. In the View Properties tab, some execution hints are set to use the RESULT_CACHE. This is the top most consuming view. Views used as data sources, which will be cached, do not need any 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