How to investigate if SQL queries in a procedure are “inline” or not?

[former_member390378]

Sometimes SAP HANA experts come across scenarios where procedure execution does not seem optimal. The challenge is to verify the root cause - it can be due to different reasons.

The final execution time of the procedure depends on how the SQL statements are compiled by SAP HANA. One of the main factors is the style in which the SQL statements in the procedure are compiled (inlined or not inlined) and this is the focus of this blog post.

Introduction

Let me first explain what SQL Inlining is. When a procedure is executed, the SQLScript compiler tries to combine SQL statements as much as possible to optimize code - this step is widely known as SQL Inlining. In most cases, it is a good thing and you want it to happen.

Now let’s look at how you can figure out whether inlining is used in a certain case or not.

As the first step, you need to collect an explain plan for the procedure call. It will write the compiled plan into the EXPLAIN_CALL_PLANS table. The OPERATOR_STRING column now tells for each of the queries executed in the procedure if they are inlined or not.

Let’s look at this in more detail, based on 3 different scenarios.

I start with an SAP HANA 2 revision 046.00 (SPS 04) system and create Table T

create column table TAB ( A int, B int, C int, I int);

insert into TAB values (1,2,3,4);

insert into TAB values (11,12,13,14);

 

Case 1: Statement inlining:

Here, I create a procedure procCaller which internally calls another procedure procInner.

CREATE PROCEDURE procInner (OUT tab2 TABLE(I int)) 

LANGUAGE SQLSCRIPT READS SQL DATA

AS 

BEGIN

     tab2 = SELECT I FROM TAB;

END;



CREATE PROCEDURE procCaller (OUT table2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN       

    call procInner (outTable); 

    table2 = select I from :outTable where I > 11;

END;

Now let's collect the explain plan for the procedure call procCaller(). You will need to give it a  statement_name to be able to find your explain plan from the table EXPLAIN_CALL_PLANS. Here I have used case1 as statement_name.

EXPLAIN PLAN SET STATEMENT_NAME = 'case1' FOR call procCaller(?);

Now let's fetch the explain plan:

SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'case1';

Focus on the column OPERATOR_STRING. There is only 1 entry (i.e. line 4) even though there are 2 select queries in the procedure. This shows that the 2 statements have been successfully inlined.

 

Case 2: Intentionally Blocking inlining using the hint NO_INLINE:

Just in case you see that the statements are inlined but you want to test how the execution time changes if the statement is not inlined.

Let's create the following procedures:

CREATE PROCEDURE procInnerWithHintNoinline (OUT tab2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN

     tab2 = SELECT I FROM T WITH HINT (NO_INLINE);

END;



CREATE PROCEDURE procCaller2 (OUT table2 TABLE(I int))

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN      

    call procInnerWithHintNoinline (outTable);

    table2 = select I from :outTable where I > 10;

END;

As you can see procInnerWithNoinline has a statement with the hint that intends to block inlining.

Let's collect the explain plan for the procedure procCaller2() call to see how the hint affects the query plan.

EXPLAIN PLAN SET STATEMENT_NAME = 'case2' FOR call ProcCaller2(?);

SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'case2';

Now there are 2 entries (line 4 & line 5) in the explain plan - one for each query. This shows us that blocking inlining worked.

If you want to see what this does to the execution time, execute the procedure.

Case 3: Statement inlining internally blocked:


There are cases where SAP HANA implicitly decides against inlining. This is when experts say "inlining is blocked". One example is when the exit handler is used in the procedure.

Below, you can see that the procedure contains an exit handler, now let's see how this impacts inlining.

CREATE PROCEDURE procExithandler ()

LANGUAGE SQLSCRIPT READS SQL DATA

AS

begin

       declare exit handler for sqlexception

       begin

       select '' as src_app, 'E' as loglevel, ::sql_error_message as LOGMSG, ::current_object_schema as hdb_schema, ::current_object_name as hdb_object, ::sql_error_code as hdb_error_code from dummy;

       end;

     

       v0 = select * from dummy;

       v1 = select * from :v0;

       v2 = select * from :v1;

       select * from :v2;

end;

Let's collect the explain plan:

EXPLAIN PLAN SET STATEMENT_NAME = 'case3a' FOR call procExithandler();

SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'case3a';

There are 4 entries (line 9,10,11,12) now. This shows that in-lining is blocked:

In the revision that we used in our example, the EXIT HANDLER requires that individual statements are executed without any optimization. Therefore, it blocks inlining.

Now let's change our example and remove the EXIT handler:

CREATE PROCEDURE procWithoutExithandler ()

LANGUAGE SQLSCRIPT READS SQL DATA

AS

begin

       v0 = select * from dummy;

       v1 = select * from :v0;

       v2 = select * from :v1;

       select * from :v2;

end;

...collect the explain plan

EXPLAIN PLAN SET STATEMENT_NAME = 'case3b' FOR call procWithoutExithandler();

SELECT * FROM EXPLAIN_CALL_PLANS WHERE STATEMENT_NAME = 'case3b';

There is only 1 entry (line 4) for 4 queries and everything is inlined again.

Conclusion

Using the cases described above, you should be able to investigate if the statements in the procedure are inlined or not.

Questions? Post a comment.

Thank you.