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 Enter Dynamic Cache name Define as below define dynamic cache ZD_ACDOCA10 on acdoca { rldnr, rbukrs, gjahr, ryear, poper, fiscyearper, periv, budat, ktopl, racct, kokrs, prctr, segment, rbusa, rfarea, rcntr, rrcty, bstat, rtcur, rwcur, rhcur, rkcur, rocur, rvcur, rbcur, rccur, rdcur, recur, rfcur, rgcur, runit, rvunit, drcrk, sum( tsl ), sum( wsl ), sum( hsl ), sum( ksl ), sum( osl ), sum( vsl ), sum( bsl ), sum( csl ), sum( dsl ), sum( esl ), sum( fsl ), sum( gsl ), sum( msl ), sum( vmsl ) } creation configurable default off ◉ As BUDAT is included, the cache size would be larger, so you would have to remove some fields like PRCTR, or have to adjust the parameter (total_size/max_cache_entry_size) to expand the cache size. ◉ The Dynamic Cache which includes “creation configurable default off” can be set to activated or deactivated with the transaction “S_DBCACHE_CONFIG”. The Dynamic Cache without this setting is always active. ◉ This custom Dynamic Cache is almost the same as the standard Dynamic Cache “FINS_DC_ACDOCA_GL” but BUDAT (Posting Date) is added to be used for G/L Balance View (I_GLACCTBALANCECUBE). Steps of the scenario 1. Before activating the Dynamic Cache i. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT) ii. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” 2. After Activating the Dynamic Cache i. Activate the Dynamic Cache with Tcd S_DBCACHE_CONFIG ii. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which stored data in the Dynamic Cache iii. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT) iv. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which accesses the Dynamic Cache. v. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT) 3. Deactivate the Dynamic Cache again i. Deactivate the Dynamic Cache with Tcd S_DBCACHE_CONFIG ii. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT) iii. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” without the Dynamic Cache Detail of steps 1. Before activating the Dynamic Cache 1-1. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT) Run Tcd DBACOCKPIT and select Diagnostics > SQL Editor, and run the following query. select * from m_dynamic_result_cache Result 1-2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” Run the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” (= running the interface View I_GLACCTBALANCECUBE directly) with Tcd RSRT pushing “Execute and Debug” and flagging Enter the Posting date from/to as below and run Result (in the following screen shot, it is drilled down with G/L Account). Back (F3) and the Statistic is displayed. DB Time: 4.4 sec (9000 Data Manager) Selected records: 6298824 (9011 DBSEL) 2. After Activating the Dynamic Cache 2-1. Activate the Dynamic Cache with Tcd S_DBCACHE_CONFIG Search with Cache name (Dynamic Cache name) or Data Source (Table). In the following case, the data source is set to be ACDOCA. The Dynamic Caches for ACDOCA are listed. Select the created Dynamic Cache “ZD_ACDOCA10” and push “Toggle DB Status”. Now “Is Configurable DB Status” is changed and the Dynamic Cache is activated. 2-2. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” for which the data is stored in the Dynamic Cache. Run the same query with Tcd RSRT in the same way as 1. DB Time: 1.8 sec (9000 Data Manager) Selected records: 6298824 (9011 DBSEL) 2-3. Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT) Access m_dynamic_result_cache with tcd DBACOCKPIT. New Cache is created. 2-4. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” which accesses the Dynamic Cache. Access m_dynamic_result_cache with tcd DBACOCKPIT. DB Time: 0.5 sec (9000 Data Manager) 2.5 Record in in the view m_dynamic_result_cache (Tcd DBACOCKPIT) 3. Deactivate the Dynamic Cache again 3-1. Deactivate the Dynamic Cache with Tcd S_DBCACHE_CONFIG 3-2. No records in the view m_dynamic_result_cache (Tcd DBACOCKPIT) 3-3. Estimate runtime of the query “2CIFIGLBALCUBE/!2CIFIGLBALCUBE” without the Dynamic Cache DB Time: 4.3 sec (9000 Data Manager)