Symptom
SAP HANA graphically modeled calculation views provide different options to leverage SQL execution behavior.
- Since SAP HANA SPS06 (revision 62) with the advanced view property “Execute In: SQL Engine” SQL execution pattern of calculation views can explicitly be enforced (see SAP Note 1857202).
- Since SAP HANA SPS09 (revision 90) even if the advanced view property “Execute In: SQL Engine” is not set, graphically modeled calculation views may implicitly make use of SQL execution and its additional optimization patterns.
Other Terms
SAP HANA Calculation View, SAP HANA Modeler, Optimizer, SAP HANA
Reason and Prerequisites
In order to benefit from implicit SQL optimizations with SAP HANA
graphical calculation views, SAP HANA SPS09 (revision 90) is required as
a minimum revision. Later revisions provide further SQL optimization
enhancements.
Solution
While the explicit option (“Execute In: SQL Engine”) to benefit from
additional SQL optimizations with graphical calculation views (see SAP
Note 1857202)
was introduced with SAP HANA revision 62, since SAP HANA SPS09 the
calculation view execution implicitly tries to leverage additional SQL
optimizations.
The optimization and execution behavior of a SQL query against a graphical calculation view since SAP HANA SPS09 can be described as follows.
For a SQL query statement against a graphical calculation view
Since SAP HANA SPS10 for expressions used in graphical calculation views it can be distinguished between the explicit use Column Store- and SQL-expressions (e.g. for calculated column expressions). Explicit use of Column store operators in expressions or use of scripted calculations views, table functions as data sources or row-based tables can hinder to leverage the full optimization, especially the additional SQL optimizations. While the optimizations steps involved will attempt convert the respective expression either into SQL operations or explicit column store operators, the mixed use SQL-operators with non-SQL operators tend to lead to scattered execution plans and may result in non-optimal performance. While all the implicit optimizations should be beneficial to most scenarios, the explicit use of e.g. column store expressions (see Note 1857202), scripted calculation views or tables functions and other explicit modeling approaches provide a mean to explicit determine and leverage a certain operation, which may well still be a preferential approach in certain scenario.
*The scenario details which benefit from this execution delegation have been continuously enriched since SAP HANA SPS09 and will be further enhanced going forward.
The SAP HANA performance analysis tools (see SAP HANA Troubleshooting and Performance Analysis Guide) help to identify if your executed query against a Calculation View did benefit from the additional SQL optimization. Either the complete execution request could be unfolded into a single SQL statement, then the query explain plan (see the referenced guide or SAP Note 2000002) shows a detailed list of relational operators reflecting the relevant query- and Calculation View-operations. Or, in case the execution request could not be unfolded completely into a single SQL statement, partial passing of operations to the SQL optimizer is indicated if the “CeQoPop” operator is shown in the SAP HANA visualization plan (see the referenced guide or SAP Note 2073964 for details on the plan visualization tools).
In rare cases, where you see the need to explicitly disable the implicit SQL optimization for a graphical calculation view, you can add the execution hint 'ce2qo_disable_unfolding' = '1' in the advanced view properties of a graphical calculation view in SAP HANA Studio.
The optimization and execution behavior of a SQL query against a graphical calculation view since SAP HANA SPS09 can be described as follows.
For a SQL query statement against a graphical calculation view
- SAP HANA composes a single execution model from all included (graphical) calculation views. For this purpose all (graphical) calculation views are unfolded into a single acyclic data flow graph.
- A rule based optimizer is responsible for optimizations like “pushing down” filters and projections so intermediate results are narrowed down earlier, or combining multiple aggregation and join operations into one node.
- Further the initial optimization evaluates to convert each operation into a corresponding SQL operator and thus to convert the complete data flow graph into a single SQL statement, which is then passed to the SQL optimizer to apply further optimizations.
- The SAP HANA SQL optimizer can then apply further optimizations like join reordering across the complete data flow graph.
The SQL optimizer also evaluates to push operations back to the best SAP HANA column store operators*, an example of which would be to delegate the execution of a calculation view star join-node to the analytic view aggregation engine.
Furthermore a cost-based optimizer evaluates over different alternative execution plans regarding best order and best choice operator variants for an optimal performance of the query execution.
Since SAP HANA SPS10 for expressions used in graphical calculation views it can be distinguished between the explicit use Column Store- and SQL-expressions (e.g. for calculated column expressions). Explicit use of Column store operators in expressions or use of scripted calculations views, table functions as data sources or row-based tables can hinder to leverage the full optimization, especially the additional SQL optimizations. While the optimizations steps involved will attempt convert the respective expression either into SQL operations or explicit column store operators, the mixed use SQL-operators with non-SQL operators tend to lead to scattered execution plans and may result in non-optimal performance. While all the implicit optimizations should be beneficial to most scenarios, the explicit use of e.g. column store expressions (see Note 1857202), scripted calculation views or tables functions and other explicit modeling approaches provide a mean to explicit determine and leverage a certain operation, which may well still be a preferential approach in certain scenario.
*The scenario details which benefit from this execution delegation have been continuously enriched since SAP HANA SPS09 and will be further enhanced going forward.
The SAP HANA performance analysis tools (see SAP HANA Troubleshooting and Performance Analysis Guide) help to identify if your executed query against a Calculation View did benefit from the additional SQL optimization. Either the complete execution request could be unfolded into a single SQL statement, then the query explain plan (see the referenced guide or SAP Note 2000002) shows a detailed list of relational operators reflecting the relevant query- and Calculation View-operations. Or, in case the execution request could not be unfolded completely into a single SQL statement, partial passing of operations to the SQL optimizer is indicated if the “CeQoPop” operator is shown in the SAP HANA visualization plan (see the referenced guide or SAP Note 2073964 for details on the plan visualization tools).
In rare cases, where you see the need to explicitly disable the implicit SQL optimization for a graphical calculation view, you can add the execution hint 'ce2qo_disable_unfolding' = '1' in the advanced view properties of a graphical calculation view in SAP HANA Studio.
Header Data
| Released On | 18.01.2016 08:15:47 | ||
| Release Status | Released for Customer | ||
| Component | HAN-DB-ENG SAP HANA DB Engines | ||
| Other Components |
| ||
| Priority | Recommendations / Additional Info | ||
| Category | Consulting |
Good information about sap HANA Training.
ReplyDeleteSAP HANA Training Institute in Delhi
Thanks for sharing great post !!
ReplyDeleteThis information you provided in the blog that is superb.It is amazing. Keep continue..
SAP online training
sap hana online training