Calculation Engine Plan Operators (CE Functions) Vs SQL Code

In this blog, I’m going explain few CE Functions and also the alternative solution for CE Functions using three tables with sample data.

The reason why I’m talking about this topic is, still some people are thinking about CE Functions, so I just want to clear the myths about CE Functions. CE Functions are alternative to SQL Script. We have total 13 CE Functions, but out of 13 , we may need CE_VERTICAL_UNION CE Function (this is used to combine the columns of different tables though they don’t have any relation), because we don’t have simple alternative solution in SQL.

CE Functions are divided into 3 categories.

1. Data Source Access Operators
2. Relational Operators
3. Special Operators

We can write SQL Code and achieve above 3 categories CE Functions.

The execution of CE Function happens within the calculation engine and does not allow a possibility to use alternative execution engines, such as L native execution (“L” – an imperative language, which can call upon the prepackaged algorithms available in the PAL of SAP HANA).
CE Operators are converted internally and treated as SQL operations, the conversion requires multiple layers of optimizations. This conversion can be avoided by direct SQL use. Be cautious before we mix and use both CE Functions and SQL, some times it may cause performance issues.

I’m going to use the below tables to explain few CE Functions & SQL.

Eg: Simple example using CE Function CE_COLUMN_TABLE and SQL.

Note: In above screenshot, I shown log, it may change based on your system configurations and load. I just shown it to understand the execution/processing time using CE Function and SQL Code.

Joining all above three tables using CE Functions:

In this scenario, I used below CE Functions.

CE_COLUMN_TABLE: Selects data from a columnar table.

CE_PROJECTION: To renames the columns i.e. to maintain same METADATA (Field names).

CE_CALC: Is used to calculate a new column.

CE_UNION_ALL: This is semantically equivalent to SQL UNION ALL statement. It computes the union of two tables which need to have identical schemas. The CE_UNION_ALL function preserves duplicates, so the result is a table which contains all the rows from both input tables.

CE_LEFT_OUTER_JOIN: Calculate the left outer join.

Scenario:

Let’s start with CE Functions, using CE Functions, join above three tables.

The Results of the above Code using CE Functions is…

The same above CE Function code is replaced with SQL Code.

In SQL Code, we don’t need to use so many functions like above CE Functions to maintain METADATA, Calculation Function, No lengthy code.

The Results of the above SQL Code is…

Note: In below screenshot, I shown log, it may change based on your system configurations and load. I just shown it to understand the execution/processing time using CE Function and SQL Code.

CE_VERTICAL_UNION: This function is used to combine the columns of different tables even though they don’t have any relation.

See the below example, it combines 3 different tables and they don’t have any relation. But if we have DUPLICATE Fields in tables, then we need to use alias names like see in below code I used DEPTID AS “DEPT”, LOCATION AS “COL”, because these fields are available in two tables.

The results of above Code is:

So, by looking at above examples, we can use SQL Code instead of CE Functions.