Purpose
Performance optimization of analytical app is crucial in S/4HANA as it causes additional system load as well as performance of analytics itself. This is great impact on business users.
This blog is about the CDS View performance optimization with Dynamic Cache in S/4HANA. With the Dynamic Result Cache, as the aggregated cached data is accessed instead of data in the source table, thus the runtime could be optimized. The cached data is updated when the data in the source is updated. Dynamic Cache can be created for a table in S/4HANA.
This blog explains
◉ The brief overview of Dynamic Result Cache
◉ How to create “Dynamic Cache” as DDIC object and how it works in the test case including
◉ The transaction to activate Dynamic Cache (S_DBCACHE_CONFIG)
◉ Status view (M_DYNAMIC_RESULT_CACHE), e.g. how many times the cache was used.
The source of Dynamic Cache must be one table currently, so that the expected optimization comes from the reduction of the record number to be selected from one source table. But I still believe it should help optimizing the runtime for the CDS View in which complex calculations using the data in the source table only are included such as VDM of G/L Account Balance View (I_GLACCTBALANCECUBE), Stock Time series view (I_MATERIALSTOCKTIMESERIES) in which complex self join calculates balance value from delta value is included, and some Compatibility View like FALGLFLEXT.
Dynamic Result Cache
What is Dynamic Result Cache?
Dynamic Result Cache is the cache for one table for performance optimization in HANA. The access to the source table is redirected to the Dynamic Result Cache if the request can be filled with the cached data.
How faster?
In the test case of this blog, Database time is 1/4 shortened. Without Dynamic Cache, the DB time is 4 sec but it is 0.5 sec with Dynamic Cache. It also depends how much the data is aggregated and how the data is processed in the CDS View or the SELECT statement to access the table.
When available?
It is available as of SAP HANA 2.0 SPS 02, or S/4HANA 1909. As of S/4HANA 1909, ABAP Dictionary object Dynamic Cache is provided.
Limitations
◉ Query Execution Frequency: A side-effect of the version garbage collection process is that it may clear data from the cache.
◉ Supported aggregation types: SUM, COUNT, AVE, MIN, MAX, but DELETE or UPDATE operations for the source table invalidates the cache including MIN or MAX.
◉ Single column tables only (Join is considered to be supported in the future).
◉ Total memory size for the cache and the memory size for one cache are limited with the parameters (Total memory size (total_size) = 10000MB, the memory size for one cache (max_cache_entry_size) = 1000MB by default). It is generally expected not to have too large cache, so having several smaller caches having different fields would be one option.
◉ Dynamic Cache might not be used for partitioned table if the query contains filter predication or range restriction which could prune some of partitions, because SQL optimizer works after the partition pruning
◉ Case1:
◉ Dynamic Cache: “create DC1 as (select a, b, sum(k) from TAB where c < 10 group by a, b) with dynamic cache”
◉ SELECT statement: “select a, b, sum(k) from TAB where c < 10 group by a, b”
Case2:
◉ AG_TAB is partitioned by _dataaging, it’s aging table
◉ create DC2 as (select a, b, _dataaging, sum(k) from AG_TAB group by a, b, _dataaging) with dynamic cache;
◉ SELECT statement: “select a, b, sum(k) from AG_TAB group by a, b with RANGE_RESTRICTION(‘CURRENT’)”
◉ If “RANGE_RESTRICTION(‘CURRENT’)” is not included in the statement, or if the source is CDS View and it includes the annotation “DataAging.noAgingRestriction:true” (to access aged data).
Test on Dynamic Cache for ACDOCA
The test scenario overview
Dynamic Cache for ACDOCA is created and the CDS View includes the table ACDOCA.
The Access to the CDS View is redirected to the Dynamic Cache instead of source table.
Test environment:
SAP S/4HANA CAL instance
◉ S/4HANA 1909
◉ HANA2 Rev46
How to create Dynamic Cache View
Firstly, Dynamic Cache including BUDAT has to be created for ACDOCA to optimize the access to I_GLAcctBalanceCube, whose runtime tends to be long as balance value is calculated from past delta values and BUDAT is used as the key to join internally. Standard Dynamic Cache FINS_DC_ACDOCA_GL is not enough as BUDAT is not included.
Steps:
From the context menu of the package tree in ADT, select New > New ABAP Repository Object > Dictionary > Dynamic Cache
No comments:
Post a Comment