Skip to Content

View Calculation Model of SQLScript Code

Is there a way to see view the calculation model that is generated from SQLScript code by the calculation engine?

I know, that there is PlanViz for visualizing the executed plan. However, it does not show nodes such as those generated for imperative code (the L-code nodes). I want to find out how the L-nodes and the other nodes are playing together, particularly if constructs such as loops are involved.

For example, if the following code is executed by PlanViz, the call would be represented by just a single "Call" node:

CREATE OR REPLACE PROCEDURE no_selects (
    out result INT
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
    DECLARE i INTEGER;
    DECLARE j INTEGER;
    DECLARE a INTEGER;
    
    FOR i IN 1..4 DO
        j=:i-1;
    END FOR;
    
    a = :j + :i;    
    result = :a * :j;
END;

call no_selects(?);

Specifically, I'm interested learning how to get the exact output of the calculation engine.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 17 at 12:31 PM

    Hi Patrick,

    The result is 21 as you might already executed your procedure

    When I prepare the visulalization plan, I can see the L-node and its details as follows:

    Name: L
    ID: sqlscript_context_r_13605_2
    Schema: ......
    Estimated Cost (Exclusive): 0
    Estimated Cost (Inclusive): 0
    Code: // SQLScript V3 Llang Code Generator
    
    
    #pragma function A00077387.NO_SELECTS
    export Void main(RSInteger RESULT & vRESULT)
    {
    
    
    
        #pragma location  line 7 col 13 (at pos 79)
        RSInteger vI__1;
    
        #pragma location  line 8 col 13 (at pos 103)
        RSInteger vJ__1;
    
        #pragma location  line 11 col 9 (at pos 153)
        RSInteger loop_var_1 = expr::typecast_<RSInteger>(1);
        Bool loop_cond_1 = expr::le_<RSBoolean>(loop_var_1,expr::typecast_<RSInteger>(4)).isTrue();
        RSInteger vI__3 = vI__1;
        RSInteger vJ__3 = vJ__1;
        while (true) {
            if (!(loop_cond_1)) { break; }
            RSInteger vI__2 = loop_var_1;
            if (expr::lt_<RSBoolean>(loop_var_1,expr::typecast_<RSInteger>(4)).isTrue()) {
                loop_var_1 = expr::add_<RSInteger>(loop_var_1,expr::typecast_<RSInteger>(1));
            }
            else {
                loop_cond_1 = false;
            }
    
            #pragma location  line 12 col 9 (at pos 175)
            vJ__3 = expr::sub_<RSInteger>(vI__2,expr::typecast_<RSInteger>(1));
            vI__3 = vI__2;
    
            #pragma location  line 11 col 9 (at pos 153)
            continue;
        }
    
        #pragma location  line 15 col 5 (at pos 208)
        RSInteger vA__2 = expr::add_<RSInteger>(vJ__3,vI__3);
    
        #pragma location  line 16 col 5 (at pos 230)
        vRESULT = expr::mul_<RSInteger>(vA__2,vJ__3);
        return;
    }
    
    Type: OP_L
    Add comment
    10|10000 characters needed characters exceeded

    • What kind of execution structure or code gets generated is dependent on the HANA version. With the SQLScript V3 compiler (as of HANA2 if I'm not wrong) the L-Code can be one of several compilation targets. There's also a near- native compilation that is used depending on the code, as well as the other options that had been available before, e.g. CE plan operators.

      Once again, the details on "what" and "how" SAP HANA does with the SQLScript are not publicly documented, so you're practically left to look at things like PlanViz and trace outputs to find more information.