Skip to Content

Intermediate table variables in hana sql script Performance

Apart from the making the code easily readable (if properly implemented), does using the table variables in hana sqlscript has any specefic performance impact over a single SQL statement ?

Add a comment
10|10000 characters needed characters exceeded

  • This is a fairly broad question. Can you provide a concrete example of what want to compare?
    A major difference is that SQLScript has its own optimisation and data flow representation. Depending on the actual execution parameters of a SQLScript block this can lead to very different SQL statements that actually get executed.

  • Case 1) eg:

    T1 = select * from tab1;

    T2 = select T1.*, tab2.C2 from T1 left outer join tab2 on ( T1.c = tab2.c);

    T3 = select T2.* , tab3.C3 From T2 left outer join tab3 on ( T2.C = tab3.C);

    T4 = select T3.*, tab4.C4 from T3 left outer join tab4 on (t3. C3 = tab4.c5);

    Outtab = select * from T4;

    Case 2) eg:

    Outtab =

    Select tab1.*, tab2.C2, T3.c3, T3.c4

    From tab1

    Left outer join tab2 on (tab1.c. = tab2.c)

    Left outer join

    (

    Select tab3.c, tab3. c3, tab4.c4

    From tab3 left outer join tab4 on (tab3.c3 = tab4. C5)

    ) As T3

    On( tab1.c = t3.c);

    To be more precise on my queries

    1) does the case 1 kind of sqlscript outperform ,in terms of execution time ,the case 2 in any case ? (Leave the case of independent table blocks and possibility of parallel execution and reusability of code result blocks,which is not happening in my case 1

    2) Does the use of table variables as in case 1 , create extra runtime memmory overhead ?

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Aug 21, 2018 at 02:21 AM

    Code examples w/o data that cannot be reproduced are really just chatting about stuff. If the goal is to figure out what's actually happening, a complete test case is unavoidable.

    I can of course offer you what I "think" and "believe" and "assume" what will happen and to what degree that will have an impact on performance, but that may or may not be what's actually happening.

    If you're fine with that, my replies would be

    1) The examples looks a bit like non-sense and contrived. If it is about hierarchy resolution, then I'd recommend to use the HANA hierarchy features instead. Anyhow, this data flow graph does resolve to a single SELECT statement that gets executed. So, here I would not expect a difference in runtime characteristics beyond the overhead incurred by using a SQLScript block.

    2) The table variables in SQLScript are not "positions in memory" like what you have in other programming languages. Instead, you can consider them "labels" for SQL blocks (comparable to common table expressions in plain SQL). Upon query execution time, SQLScript resolves the labels and creates independent data flow graphs - if that is possible and beneficial. Eventually, what gets executed are SQL commands and whether or not those create intermediate result sets has no connection to the table variables.
    So the answer is NO, the table variable doesn't force extra runtime overhead (but handing data between different SQLScript blocks/procedures/functions may...).

    That is as much as I can say about these theoretical examples.
    Common practice with different customer projects so far has shown that using table variables helps a great deal in making code clearer and also in identifying where and why performance issues occur.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.