SAP HANA SQL scripting optimization: the CE Functions

In SAP HANA, you have two possibilities to create the Calculated Views:

• Using the graphical method
• Using the scripting method with CE functions

In this blog, I will demonstrate that CE Functions can improve performances from a Calculated View.

First, I will give you some general information regarding the CE Functions. After that, I will show you the two different ways to create a Calculated View. And at the end, I will compare their performances using a SQL select.

If you want to have more information regarding the SAP HANA technology, don’t hesitate to assist at the next dbi services event:
https://www.dbi-services.com/newsroom/events/

CE Functions

The CE Functions encapsulate data-transformation functionalities. They constitute an alternative to using SQL statements as their logic is directly implemented in the Sap HANA CALC engine. Direct use of the CALC engine allows implementers to influence the execution of a procedure or a query which, in some cases, is more efficient.

In the table below, you can find the list of the CE Functions and their use cases:

How to create a Calculation View

As I say at the beginning, you have two methods to create a Calculation View in Sap HANA:

• Using the graphical method
• Using the Scripting method

Using the graphical Method

Right click on a package and select “New / Calculation View”

Select the “Graphical” type

Create your view following the steps below

1. Select type of operation you want to perform
2. Select your sources
3. Join your sources
4. Select the object you want to use in your Calculation view
5. Join your “Aggregation operation” to the “Aggregation” box

Create your Calculation View Layout

1. Click on the “Aggregation” box
2. Select the object you want to have in your layout
3. Check if the object are selected and add, if needed, calculated columns

Check the objects

1. Click on “Semantics” box
2. Select the type of the objects
3. Validate and activate the view

Using the “SQL scripting” Method

Right click on a package and select “New / Calculation View”

Select the “SQL Script” type

Create your view following the steps below

1. Click on the “Script View” box
2. Type our SQL script
3. Introduce the CE functions

Check the objects

1. Click on “Semantics” box
2. Select the type of the objects
3. Validate and activate the view

SQL Performance comparison

Goal of the test

In this part, I will compare the SQL performance from two calculated views that have been built with the two different methods:

• “Graphical” method
• “SQL scripting” method

Description of the test

The same SELECT query will be send to the database and we will check the Server time response. The two SELECT will use a different calculated view as data source:

• CAL_AMOUNT (graphical method)
• CAL_AMOUNT_CE (SQL Scripting method)

Test with the “Graphical” calculated view

SQL Query

Performance

Test with the “SQL Scripting” calculated view

SQL Query

Performance

Performance decoding

Why the use of these CE functions boost the performances of your queries? The explanation is located in the query execution plan and especially in the use of the CALC engine from the SAP HANA database.

When you send a “normal” SQL query in the SAP HANA database, the CALC engine is not used. The SQL parser send the query directly to the “Database optimizer” to optimize the execution of the query (1).

When your SELECT query uses a calclated view with CE functions, the CALC Engine optimizes the calculation model.

In our case, when we analyze the “normal” SQL query, the “calculation search” task has been split in 3 different sub-queries that can’t start at the same time.

But with the optimized calculated view, we can remark that there is only 2 sub-queries.

Conclusion

The use of CE functions in the creation of calculated views can significantly accelerate the execution of your SQL queries. The CALC engine from SAP HANA is optimize to use these functions.

There’s only one restriction using this kind of functions. The performance will dramatically reduce if you try to create a SQL query mixing “normal” and “optimized” calculated views.