## 4.4.2  Dynamic Result Cache
The dynamic result cache may be used to improve the performance of queries which are frequently executed.
In comparison to the static result cache, running queries against a dynamic result cache may be a better option for increasing the performance of queries. The dynamic result cache offers improved throughput and response time but most importantly it eliminates the risk of querying stale data and will always return transactionally consistent data. However, dynamic caches are not suitable for all views, the performance gains depend very much upon the nature of the data, the query, and how frequently the cache is read.
Optimal scenarios where the dynamic result cache can dramatically improve performance include the following characteristics:
- Intensive parallel querying of large tables
- Extensive use of aggregation
- Tables are regularly updated and up-to-date query results are essential.
An SAP Note about the Dynamic Result Cache is available: SAP Note 2506811 FAQ: SAP HANA Dynamic Result Cache .
## Overview
You specify the cache type when you create the cache; only one type of cache can be applied to a view, either static or dynamic. A key difference is that the dynamic result cache has no retention period:
ALTER VIEW V ADD STATIC CACHE RETENTION 10;
ALTER VIEW V ADD DYNAMIC CACHE;
Dynamic result caches require no retention period because up-to-date query results are guaranteed. When a query is executed against a dynamic result cache there are two possible responses:
- In the best-case scenario up-to-date results are returned by firstly incrementally updating the cache with delta records of newly inserted, updated or deleted data and then returning the query result from the cache.
- In some situations an incremental update of the cache is no longer possible as delta records are not identifiable due to version garbage collection; in this case the 'fallback' option is then invoked and the query runs against the database. A full refresh of the cache is then executed in the background in a separate thread.
The decision to either read from the cache or the database is taken during execution of the query. Events which invalidate and prevent use of the cache include the following:
- The cached data has been invalidated by garbage collection (see details below).
- The volume of delta records has reached its maximum permitted size of 10,000,000 (planned to be changed in a future release).
In any of these cases the fallback option is used instead of the cache.
## Scope
Dynamic result caches are only supported for SQL views defined as aggregation on single column tables; aggregation types sum, count, and avg are supported. MIN and MAX aggregation is partially supported for INSERT (see below).
## Benefits and Limitations
Some of the factors influencing the benefits gained from using dynamic result cache are discussed in more detail here:
Query Execution Frequency : A side-effect of the version garbage collection process is that it may clear data from the cache. To a certain extent the frequency of the garbage collection can be controlled by setting the blocking period (see configuration below), but you should consider the negative impact this will have on the general performance of all SELECT and UPDATE statements accessing the target table. If the cache is not used within the blocking period then the cache will be invalidated by garbage collection and must be refreshed. A querying frequency against the cached view of less than the garbage collection blocking period (default 1 minute) is therefore essential.
Table Size and Cache Size : Performance of the dynamic result cache is proportional to the cache size. A performance gain can only be expected if the dynamic result cache has a significantly smaller cache size than the base table.
Aggregation : One of the greatest benefits of cached views is in reducing the need for expensive aggregation processes. If your data has a high degree of aggregation then using cached views may be beneficial. Queries with highly-selective filters, on the other hand, requiring only a few milliseconds for aggregation processing should expect no benefit from using dynamic result cache.
Base Table Characteristics : If the base table of the view is mostly used to INSERT data then a dynamic result cache can be defined effectively in combination with all supported aggregation types: SUM, COUNT, AVG, MIN, MAX. However, DELETE and UPDATE operations on the base table in combination with MIN or MAX aggregation invalidate the cache and reduce the potential performance improvement. It is therefore not recommended to use dynamic result cache with MIN/MAX aggregation if the target table is not an INSERT only table.
## Related Information
```
SAP Note 2506811
```