SAP HANA Tutorial, Material and Certification Guide

Caching is used widely in SAP HANA as a strategy to improve performance by re-using queried data rather than re-reading and processing the data every time it is requested. The static result cache and the dynamic result cache are applications of this.

The static result cache is created for a specific view and remains valid for the duration of a user-defined retention period. The dynamic result cache is similar but does not have a retention period; it guarantees transnational consistency by maintaining delta records of all changes applied to the underlying table.

Static Cache vs Dynamic Cache

Feature	Static Result Cache	Dynamic Result Cache
Target Scenario	Scalability enhancement for query on complex view (usually top-level view) from an application which can accept stale data.	Scalability enhancement for heavy aggregation workload on big tables which are frequently updated (for example ACDOCA).
Query result	Stale data	Non-Stale data
Scope	SQL View, User-defined table function (w/o imperative logic), Calculation view with some limitation. Aggregation types: SUM, MIN, MAX, COUNT.	SQL Views on the aggregation of a single column table. Aggregation types: SUM, COUNT, AVG – fully supported. MIN, MAX- partially supported for insert only table.
Cache Maintenance	Whenever the cache period becomes older than the retention period then the cache is fully refreshed.	-If updated records are identifiable then the cache is incrementally updated with updated records. -If no update is identifiable (due to MVCC garbage collection) then the cache is fully refreshed.

Static Result Cache

Basic Concept

Query result on complex view is cached and refreshed periodically. The maximum staleness restriction can be specified by users.

Features

Ease of use: No application modification necessary

Selectively enabled for sets of queries using a view definition (DDL) statement

Two types of view caches:

– aggregated cache that allows explicit aggregation types for caching (MIN, MAX, COUNT, SUM)

– parameterized cache (caching of table function)

Configuration

indexserver.ini -> [result_cache] -> enabled = yes (default: no);

indexserver.ini -> [result_cache] -> total_size = <MB> (default: 10000);

How to use

View related DDL:

CREATE VIEW SIMPLE_VIEW AS (…) WITH CACHE RETENTION 100;

ALTER VIEW <view_name> DROP | ADD | ALTER CACHE …;

Table function related DDL:

CREATE FUNCTION SIMPLE_FUNCTION WITH CACHE RETENTION 100;

Monitoring Views:

SELECT * FROM M_RESULT_CACHE; shows cache-related information, e.g. cache id, cache key, memory size, record count

SELECT * FROM M_RESULT_CACHE_EXCLUSIONS; list of excluded views from caching

System Commands:

ALTER SYSTEM CLEAR RESULT CACHE;

ALTER SYSTEM REFRESH RESULT CACHE <object_name>;

Example

In order to demonstrate on static cache, I have created a simple calculation view CA_PFC_COMBINED_FACT_RC and it uses a column table PFC_COMBINED_FACTS as source table. this view returns a bunch of measures grouped by PFCDIK.

SAP HANA Cert Exam, SAP HANA Learning, SAP HANA Tutorial and Materials, SAP HANA Prep, SAP HANA Guides

Let’s do a data preview for this view. it takes 3.5 seconds to execute the query.

and if you highlight the SQL statement generated and click with ‘Explain Plan’

It shows that the view result is aggregated from column table PFC_COMBINED_FACTS directly

Let’s run the ALTER VIEW statement to enable static cache for this view and for retention we assign 100 minutes

ALTER VIEW "_SYS_BIC"."Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC" ADD CACHE RETENTION 100

The following system views can help us to find out a lot of information about cached views, columns and properties.

-- to check view metadata

SELECT schema_name, view_name, has_cache

FROM views

WHERE view_name = 'Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC';

--shows all cached views and table functions

SELECT * FROM RESULT_CACHE;

--shows all cached columns of cached view and table functions

SELECT * FROM RESULT_CACHE_COLUMNS;

The system view VIEWS shows this calculation view has been enabled for static full cache

The system view RESULT_CACHE lists all cached views

The system view RESULT_CACHE_COLUMNS lists all cached columns

Let’s add static cache for this view so we can see the difference for performance.

But before start we need to check monitor view M_RESULT_CACHE to see if cache has been added

till now cache has been enabled but not added yet. We need to run the SQL statement to populate the result cache

SELECT

"PFCDIK",

sum("FRMLNUM1") AS "FRMLNUM1",

sum("FRMLNUM2") AS "FRMLNUM2",

sum("FRMLNUM3") AS "FRMLNUM3",

sum("FRMLNUM4") AS "FRMLNUM4",

sum("FRMLNUM5") AS "FRMLNUM5",

sum("FRMLNUM6") AS "FRMLNUM6",

sum("FRMLNUM7") AS "FRMLNUM7",

sum("FRMLNUM8") AS "FRMLNUM8",

sum("FRMLNUM30") AS "FRMLNUM30",

sum("FRMLNUM31") AS "FRMLNUM31",

sum("FRMLNUM34") AS "FRMLNUM34",

sum("FRMLNUM39") AS "FRMLNUM39",

sum("BALNOMVAL") AS "BALNOMVAL"

FROM "SCD"."PFC_COMBINED_FACTS"

GROUP BY "PFCDIK"

Statement 'SELECT "PFCDIK", sum("FRMLNUM1") AS "FRMLNUM1", sum("FRMLNUM2") AS "FRMLNUM2", sum("FRMLNUM3") AS ...'

successfully executed in 3.312 seconds (server processing time: 3.294 seconds)

Fetched 1000 row(s) in 75 ms 32 µs (server processing time: 2 ms 215 µs)

Result limited to 1000 row(s) due to value configured in the Preferences

The first run seems pretty normal and performance also looks like same but let’s check cache monitoring view again

WOW static cache has been populated already do we expect performance improvement at next run?

Let’s execute the SQL statement again and this time it takes only 22 ms (0.022 sec) instead of 3.2 sec to execute the query.

The ACCESS_COUNT column specifies the number of accesses on the cache entry. Every access on the cache increases hit 1 until cache retention exceed

You can refresh cache by running the ALTER SYSTEM statement

ALTER SYSTEM REFRESH RESULT CACHE "_SYS_BIC"."Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC"

and the REFRESH_COUNT will be increased after cache refresh.

Let’s take a look at the Explain Plan and Execution Plan. The Explain Plan shows the view result is from result cache, not from column table

Execution plan shows the view result is from result cache as well

Of course, you can wipe out the result cache by running the following ALTER SYSTEM statement

ALTER SYSTEM CLEAR RESULT CACHE

Or just disable result cache for this view by running the ALTER VIEW statement

ALTER VIEW "_SYS_BIC"."Developer_Test.DLIU/CA_PFC_COMBINED_FACT_RC" DROP CACHE

After cache is dropped for this view let’s check system view again

Also flag HAS_CACHE has been changed from ‘STATIC,FULL’ to ‘NONE’

Dynamic Result Cache

Basic Concept

The dynamic result cache may be used to improve the performance of queries which are frequently executed but most importantly it eliminates the risk of querying stale data and will always return transitionally consistent data.

Optimal scenarios

◉ Intensive parallel querying of large tables

◉ Extensive use of aggregation

◉ Tables are regularly updated and up-to-date query results are essential.

Configuration

indexserver.ini -> [dynamic_result_cache] -> enabled = yes (default: no);

indexserver.ini -> [dynamic_result_cache] -> max_cache_entry_size = <MB> (default: 1000);

indexserver.ini -> [dynamic_result_cache] -> total_size = <MB> (default: 10000);

ndexserver.ini -> [dynamic_result_cache] -> version_garbage_collection_blocking_period= 60;

How to use

SQL view only:

CREATE VIEW SIMPLE_VIEW AS (…) WITH DYNAMIC CACHE;

ALTER VIEW <view_name> DROP | ADD CACHE …;

System Views:

SELECT * FROM DYNAMIC_RESULT_CACHE;

SELECT * FROM RESULT_CACHE_COLUMNS;

Monitor Views:

SELECT * FROM M_DYNAMIC_RESULT_CACHE;

SELECT * FROM M_DYNAMIC_RESULT_CACHE_EXCLUSIONS;

System Commands:

ALTER VIEW … DROP DYNAMIC CACHE;

ALTER SYSTEM CLEAR DYNAMIC RESULT CACHE; –remove all cache entries

ALTER SYSTEM REMOVE DYNAMIC RESULT CACHE; –remove a single named cache entry

Dynamic Cache Example

Let’s create a SQL view with dynamic cache enabled

CREATE VIEW "DLIU"."MYDYNAMICCACHEVIEW" as (

SELECT

"PFCDIK",

sum("FRMLNUM1") AS "FRMLNUM1",

sum("FRMLNUM2") AS "FRMLNUM2",

sum("FRMLNUM3") AS "FRMLNUM3",

sum("FRMLNUM4") AS "FRMLNUM4",

sum("FRMLNUM5") AS "FRMLNUM5",

sum("FRMLNUM6") AS "FRMLNUM6",

sum("FRMLNUM7") AS "FRMLNUM7",

sum("FRMLNUM8") AS "FRMLNUM8",

sum("FRMLNUM30") AS "FRMLNUM30",

sum("FRMLNUM31") AS "FRMLNUM31",

sum("FRMLNUM34") AS "FRMLNUM34",

sum("FRMLNUM39") AS "FRMLNUM39",

sum("BALNOMVAL") AS "BALNOMVAL"

FROM "DLIU"."PFC_COMBINED_FACTS"

GROUP BY "PFCDIK"

) WITH DYNAMIC CACHE;

If you want to enable existing SQL views you can simply run ALTER VIEW ADD DYNAMIC CACHE

ALTER VIEW "DLIU"."MYDYNAMICCACHEVIEW" ADD DYNAMIC CACHE

The following system views can help us to find out how many views and columns have been enabled for dynamic cache in the system.

-- to check view metadata

SELECT schema_name, view_name, has_cache

FROM views

WHERE view_name = 'MYDYNAMICCACHEVIEW';

SELECT * FROM DYNAMIC_RESULT_CACHE;

SELECT * FROM RESULT_CACHE_COLUMNS;

This SQL view has been enabled for dynamic full cache

The system view DYNAMIC_RESULT_CACHE lists all enabled dynamic cache views

The system view RESULT_CACHE_COLUMNS lists all enabled dynamic cache columns

Before we execute the sql statement to add dynamic cache let’s check system view M_DYNAMIC_RESULT_CACHE to see if dynamic cache exists

Ok it is clear the dynamic cache has been enabled but not added yet. We can execute the SQL statement to populate the dynamic cache

SELECT * FROM "DLIU"."MYDYNAMICCACHEVIEW"

Right after we execute and let’s check the monitoring view again to see if there is any change. Ok the dynamic cache has been added.

Let’s rerun the SQL statement and this time it takes only 25 ms instead of 3.6 seconds to execute the query. dynamic cache takes effect

Let’s take a look at both Explain Plan and Execution Plan

The Explain Plan shows the view result is from dynamic result cache, not from column table

Execution plan shows the view result is from dynamic result cache as well

What about executing some INSERT operations in the column table and can you expect fresh (not stale) data with the same performance?

INSERT INTO "DLIU"."PFC_COMBINED_FACTS"

(

"PFCDIK",

"PFCHOLIK", --it is mandatory field but not required for view

"FRMLNUM1",

"FRMLNUM2",

"FRMLNUM3",

"FRMLNUM4",

"FRMLNUM5",

"FRMLNUM6",

"FRMLNUM7",

"FRMLNUM8",

"FRMLNUM30",

"FRMLNUM31",

"FRMLNUM34",

"FRMLNUM39",

"BALNOMVAL"

)values (

1000, -- "PFCDIK"

1000,

800.00,

800.00

)

After inserting new row into column table let’s run the SQL statement and this time it takes only 34 ms to execute the query. it take bit longer but data is up to date. We can see the new row returning from query with good performance. It looks like the cache has been updated dynamically.