## 4.4.1  Static Result Cache
Complex SQL views and calculation views place a heavy load on the system. You may be able to use the static result cache to reduce CPU consumption (and thus to increase overall system throughput) or to reduce the response time of queries.
Frequently accessed views may consume an excessive amount of CPU. Cached views can trade this CPU consumption for an increased memory consumption by caching the result of evaluating a view and reusing this cached data when the view is accessed by a query.
Using the static result cache the query result of a view is cached in memory and refreshed only periodically. On the one hand, this avoids having to reevaluate the view each time the view is accessed, on the other hand, the cached data might be stale if the tables used by the view are updated after the last refresh. However, the data retention period which determines how old the data may become is configurable and the age of the cached data is also returned to the database client.
Cached views must be implemented in combination with careful testing to validate that correct results are returned, that the cached views are actually used, and that indeed the CPU consumption is reduced. Tools which can help with this are available in SAP HANA cockpit and SAP HANA studio such as Explain Plan and Visualize Plan (see separate sections of this document).
## Scope
Generally, caching can be used for calculation views, SQL views, table functions which return the result of a single SQL SELECT statement, and CDS views (which are translated into table functions). To save memory, cached views store aggregated values (aggregation types SUM, MIN, MAX, and COUNT are supported).
Not all views may be suitable for caching (for example views using time functions which depend upon the current time would not be suitable). Caching cannot be used for scripted views, join views, OLAP views, and hierarchy views. Other limitations may apply, for example, if analytic privileges have been applied to the data.
## Configuring the Memory Budget
A maximum cache size (a memory budget) must be defined to manage the maximum amount of memory available to cache cached views. This is configured in the indexserver.ini file in the total_size parameter in the result_cache section of the file.
| Parameter   |   Default | Detail                                             |
|-------------|-----------|----------------------------------------------------|
| total_size  |     10000 | The memory budget in MBavailable for cached views. |
If the memory consumed for caching views exceeds this threshold an alert is raised and, where necessary, cached views will be dropped from memory resulting in higher response times for previously cached data. In this case administrators may increase the memory budget size or create capacity in the budget by, for example, dropping cached views which are rarely used.
If a view fails to be created then it is added to an exclusion list of views which have not been successfully cached (view m_RESULT_CACHE_EXCLUSIONS). There are two common cases where this occurs:
- If the time to calculate the result of the view is longer than the retention time.
- If the memory required to cache the result of the view is larger than the memory budget.
The following example query selects basic details of failed cached views:
```
SELECT SCHEMA_NAME, OBJECT_NAME, REASON, EXCLUDE_TIME FROM M_RESULT_CACHE_EXCLUSIONS
```
## Hints to Enable the Result Cache
The static result cache feature is disabled by default and is only considered when explicitly invoked by using one of the following RESULT_CACHE hints in the SQL statement :
| Parameter                             | Detail                                                                                                              |
|---------------------------------------|---------------------------------------------------------------------------------------------------------------------|
| HINT(RESULT_CACHE)                    | Always use the result cache if it is available.                                                                     |
| HINT(RESULT_CACHE_MAX_LAG( seconds))  | Sets the retention period of the result cache to this value (or the value set in the ADDCACHEconfiguration).        |
| HINT(RESULT_CACHE_NON_TRAN SACTIONAL) | Allows join or union operations using the result cache entries and disregards possible transaction inconsistencies. |
| HINT(RESULT_CACHE_NO_REFRE SH)        | Access existing cached data without refreshing it even if its retention period is over.                             |
Note that the hint class for result cache is also supported: RESULT_LAG('result_cache'[, seconds])