on 07-25-2023 5:57 PM
Hello,
sometimes for SIMPLE calculations in graphical views we experience OOM issues.
How this happens:
consider this calculated column:
TO_DATE( TO_VARCHAR( SALESDOCUMENTDATE, 'YYYY') || '0101', 'YYYYMMDD').
This will trigger a temporary ROW STORE materialization visible in explain plan.
if I remove the calculated column : no ROW SEARCH step appear:
This do not affect too much performances in case of few columns many rows.
In case of many columns and few rows ( like 300+ columns available in SD standard BI Content made by SAP ).
We experience a x100 factor in memory usage during View persistency, and partitioning do not help too much.
So for ~ 4 Million rows times ~ 300 columns we hit over 20GB of memory during execution.
While the persisted View account only for 200MB.
This do not ends here,
if the views are used by third party BI tools (with 1 daily full import to the other BI tool, so a "select *" statement is issued ),
Just adding a simple calculation as shown above will trigger OOM issues.
This issues are not visible during modelling and MAY involve colleagues from other teams (sys admins), to analyze in hana cockpit / SQL console. This is a real big issues as in big projects 30% of the time is spent in this kind of optimization ( that we can call "bug hunting" ).
I have 2 questions:
1) When Row Engine will be completely removed in favour of ESX ?
2) Is there a way to know which function available in graphical view editor will trigger a row store temporary materialization ? ( can someone add a sort of "black pirate flag" to spot this ? ).
Best Regards,
Alberto
HI Alberto,
Are you added calculated column at very bottom of your view?
Have tried to force engine with hints?
Kind Regards,
Dominik
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Dominik,
the HANA cloud database in my case is managed by the application (SAP Datasphere),
So I have not a user/login with privileges to change configuration parameters.
The procedure to persist a view is triggered and managed by the application, so I can not enter an execution hint on it.
Best Regards,
Alberto
User | Count |
---|---|
77 | |
9 | |
7 | |
6 | |
6 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.