SQLscript procedure - Avoid materialisation of Internal tables

[Former Member]

Hello Experts,

I'm currently tuning a massive SQLscript procedure and looking for pointers from people who have done similar exercises before.

What I noticed in the current procedure is,

There are a couple of internal tables,

which is then used for some intermediate processing - which is again stored in an internal table.

The var_out is then generated by joining a few of these intermediate internal tables.

I feel this is forcing the procedure to run sequentially(wait till all the intermediate results are materialised before building resultset),

and thought of constructing a massive select with all intermediate selects as sub-queries.

My PO is not in favor of this idea as this decreases readability of code ( which kinda makes sense ).

Then i started exploring for ways to achieve this without changing code ( i.e have temp tables in procedure but make the

compiling/execution happen by constructing a massive select statement ).

I found some hints which looked like they can help but,

also could not find any examples or documentation for them.

NO_CS_ITAB_IN_SUBQUERY - Prefer/Avoid column engine itab in subquery.

Can someone share some insights ,

1. If this is the right direction to go?

2. How do we do we achieve this keeping the readability of code intact?

Thanks,

Vinay

[lbreddemann · Accepted solution]

You shouldn't consider using hints to fix your performance problem.

Very likely the design of the procedure is leading to the (unwanted) materialization of intermediate result sets, so this is where you should look for a fix here.

One major misconception in this area typically is to assume that the assignment of a query result to a table variable leads to materialization,

Although possible, this is not the main function of this assignment.

Instead the table variables can be seen as nodes in a data flow graph and SAP HANA will combine and optimize this data flow graph upon execution.

So, as long as you're not actually filling temporary tables in this procedure, this all is happening automatically and doesn't affect readability of your code at all. Same with setting the procedure to execute sequentially - this is just one key word in the header of the procedure, nothing dramatic.

Finally, as far as general tips go, the more granular you can make the single SELECT statements the better the options for late materialization and query optimization.

So, you might start to look for really complex big statements and try to break those down into easy digestible units.

[Former Member · Accepted solution]

Hello Lars,

Thanks a lot for your response.really appreciate it.

I completely understand what you're saying, on a conceptual level and makes perfect sense.

Well, in my case there are a bunch of select statements ( some with unions) and are assigned to temp-variables which are then used to construct var_out.

So going by what you're saying,hana should not materialise at every table variable and should materialise only when necessary, all good till now.

But my problem is how can i check this? there is no way to check execution path for a stored procedure ( atleast I'm not aware of),

visualise plan gives way too much information (although i could see several parallel blocks which might mean it is indeed parallelising ).

Is there any way to check what you are saying in the system to be sure what exactly is happening.

[lbreddemann · Accepted solution]

The plan Viz tool can indeed be used for calls to a stored procedure.

And with that you can find if and where materialization occurs.

Yes it does provide a lot of information, but focusing on data flows and using the filter options as e.g. explained by here gets you quite far in your analysis.

[Former Member · Accepted solution]

Hi Lars,

That was the exact information I was looking for,

thank you very much.

I could see the statement generated at runtime, materialisation and time taken for each of these statements. Couldnt've asked for more.

Thanks to you too .

Best Regards,

Vinay