Last change on 01/28/2018

This blog is part of the blog series HANA based BW Transformation.

The following list is based on experiences with real customer messages and is intended to help customers learn from the mistakes of other customers.

9.1 SQL Script Procedures

Some SQL Script best practice tips could be found in chapter »Best Practices for Using SQLScript« inside the SAP HANA SQLScript Reference.

Here are some general SQLScript recommendations:

Do not mix the functions of SQLScript and CE functions within a script. Example: Do not code SQLScript and read inside the SQLScript from a procedure or CalcView with CE Functions). Pass on all parameters as required to the lowest level Reduce the size of the data set as early as possible in the data flow. This can be done by applying static filters or applying dynamic filters using parameters at the lowest level Minimize the size of any intermediate result sets in complex processing. Do not include any extra attribute columns that may increase the rows of aggregated results being passed between nodes in the data flow. Avoid extremely large intermediate result sets, even if they will be reduced or aggregated before the final result is returned to the client. These can have high memory requirements. Perform calculations at the last node possible in the data flow (ideally done in final aggregation or projection). This will reduce the amount of data rows on which you are performing the calculations also reduce the number of columns being transferred from one node to another. In some situations, you can use a union and then aggregate the results instead of using a join for better performance. Reduce Complexity of SQL Statements: Break up a complex SQL statement into many simpler ones. This makes a SQLScript procedure easier to comprehend. Identify Common Sub-Expressions: If you split a complex query into logical sub queries it can help the optimizer to identify common sub expressions and to derive more efficient execution plans. Understand the Costs of Statements: Employ the explain plan facility to investigate the performance impact of different SQL queries used in scripts. Reduce Dependencies: As SQLScript is translated into a dataflow graph, and independent paths in this graph can be executed in parallel, reducing dependencies enables better parallelism, and thus better performance.

The following listed SQL Script issues are related to SQL Script in the context of BW.

Within the BW all fields are defined as NOT NULL columns. Therefore, the developer is responsible to ensure that all data inside the result table of a SQL Script routine are initialized type conform. The SQL function COALESCE could be used to avoid NULL values and ensure that all values would be initialed correct in case of NULL values. Characteristic sample:

COALESCE( inTab."MATERIAL", '' ) AS "MATERIAL"

In case of numeric field, it is important to initialize the field by a numeric value. The default initial value for a numeric field is 0 and not ‘’. Key figure sample:

COALESCE( inTab."KEYFIGURE", 0 ) AS "KEYFIGURE".

The optimizer tries to push down the filter as far as possible to the source to keep the data volume as small as possible. It may have a negative performance effect if the filter push down is prevented.

The calculation engine tries to optimizer the SQLScript coding in order to compile all local select statements to one select statement. Therefore the single statements would be in-lined. Sample:

Original statements:

tab = select A, B, C from T where A = 1;

tab2 = select C from :tab where C = 0;

In-lined statement:

tab2 = select C from (select A,B,C from T where A = 1) where C=0;

For further information about in-lining see HINTS: NO_INLINE and INLINE. The hints could also be used to force or prevent inlining for a single select.

A further option to prevent inlining is to put the coding into a SEQUENTIAL EXECUTION block. All statements inside a SEQUENTIAL EXECUTION block (BEGIN SEQUENTIAL EXECUTION … END;) would not been in-lined and would be executed sequentially. The force of sequential execution prevents parallelization!

Also the usage of Imperative SQLScript Logic prevents that the optimizer can inline the single SQL statements. Therefore the recommendation is to use declarative SQLScript logic to implement the SQL script procedures in the BW transformation context, if the logic allows it.

Use of imperative SQLScript logic

The procedure SQL code would be translated into L-coding in case of using structure control elements like LOOPs or IF-conditions or declaration of local variables, see Figure 9.1.

Figure 9.1: Resulting CeLjitPop for converted SQL Script code to L

Use

hints the sequential execution block and imperative SQLScript logic

with care!

Sometimes it makes sense to test if one of this features would increase the performance.

The following statement

SELECT 'A' || ' ' || 'B' || ' ' || 'C' FROM ;

within an AMDP method will lead into the result 'ABC'.

The expected blanks are not available. To get the expected result it is necessary to set the space by using the CHAR Function (String).

The following statement

SELECT 'A' || CHAR(32) || 'B' || CHAR(32) || 'C' FROM ;

delivers the expected result 'A B C'.