Skip to Content

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.

9.1.1       Initial values

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”.

9.1.2       Avoid preventing filter push down

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.2.

Figure 9.2: 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.

 

9.1.3       Concatenate in SQL Script within an AMDP Method (Added on 08/03/2017)

The following statement

SELECT 'A' || ' ' || 'B' || ' ' || 'C' FROM <any table>;

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 <any table>;

delivers the expected result 'A B C'.

 

 

… coming soon…

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Sebastian Gesiarz

    Hello Torsten,

     

    Looking forward to the continuation. Keep up the good work!

    It would be great if you could share some loop & lookup code samples (like in ABAP with field symbols).

     

    Best regards,

    Sebastian

    (0) 
  2. Florian Birnkraut

    Hello Torsten,

    you mentioned in your blog that it is possible to join the :inTab with an DSO, like the following:

    outTab = SELECT  intab."DOC_NUMBER",
     intab."RECORDMODE",
     FROM :inTab as intab
     inner join "_SYS_BIC"."system-local.bw.bw2hana/FSDAHD01" as sd
     and  sd."DOC_NUMBER" =  intab."DOC_NUMBER";

    We have here the problem that (can be seen in Plan PlanViz) the system will extract all data from

    “_SYS_BIC”.”pr.sd/DPRSDL016″, that leads to high usage of memory.

    If we don’t use the generated view (in DSO Externe SAP-HANA-Sicht für Reporting) and instead use the active table from the dso “/BIC/AFSDAHD01”, the system extracts only the data records which are needed and results in less (appr. only 20% of scenario before) usage of memory.

    We use a classical DSO, the generated view is an analytical view.

     

    Any suggestions?

    Best Regards,

    Florian

    (0) 
    1. Torsten Kessler Post author

      HI,

      it’s okay to use the active table here.

      as long as we do not offer a SQL Script API to determinate the active DSO table it is okay to use the hard coded name.

      but keep in mind if you later migrate to BW/4 the classic DSO will be migrate to an advanced DSO, that means also the active table name here will change.

      Our up coming migration tool will point such coding blocks, but from current point of view the coding must be handle by the customer.

      Torsten

      (0) 
      1. Florian Birnkraut

        Hi Torsten,

        Firstly thanks for your answer.

        We plan later also to use an nls (license and system exist but DAPs aren’t created), the recommendation is here to use the generated view, because otherwise the archived date would not be read.

        For me the behavior of the hana database seems to be like a bug.

        Normal behavior should if I join table A with table B and table A has 1000 entries (which are also the different keys) and B has 100 mio entries with 2000 entries which are equal to table A (according to the join statement key) that the database only extracts the 2000 entries from table B and not the complete table.

        This seems to be work correctly if we use the active table of the dso but if we use the generated view it doesn’t fit.

        Any other suggestions…we opened also a ticket…

        Regards,

        Florian

        (0) 
  3. Anand Kotha

    Hi Torsten,

    Nice document on HANA based BW Transformation – guidelines and recommendations. Keep up the good work!!. Looking forward your continuation in other topics too 🙂

     

    BR/

    Anand Kotha

     

     

    (1) 

Leave a Reply