Recursive Procedures in HANA

[Former Member]

Hi Experts,

I have requirement in HANA in which i need to create a procedure which is recursively called to get the Bill Of Material information.

Can some one please guide me how can we write recursive procedures in HANA.

I heard that there are some issues with recursive procedures with which HANA cant create execution plan. Is that is true?? If yes, what would be the alternative for this??

Thanks in advance.

Best Regards,

Krishna.

[Former Member · Accepted solution]

Yes HANA procedures must be non-recursive. Recursion breaks the way HANA processes parallelism.

You need to write your procedure as a set function. Without knowing the details it's hard to provide more advice. Do avoid loops if at all possible. They kill performance.

[Former Member · Accepted solution]

Hi John,

Thank you for the valuable information.

In my case i have to process the procedure recursively to get the Bill Of Materials (BOM) for PP Module. So i have to choose looping option here if HANA dont support recursive procedure.

Could you please explain "Procedure as a set function". I didnt understand what it is.

Thanks in advance.

Best Regards,

Krishna.

[Former Member · Accepted solution]

Here is a link that explains set and procedural processing in SQL.

http://www.codeproject.com/Articles/34142/Understanding-Set-based-and-Procedural-approaches

If you need to loop then you are probably better off writing ABAP.

[Former Member · Accepted solution]

Hi John,

Thanks a lot. Really very helpful link.

Best Regards,

Krishna.

[former_member399057 · Accepted solution]

Hi John,

as far as I know BOM explosion levels are undefinable, not a definiable as in the sample for exployee~manager (here 2 levels eg.). How could one avoid a loop in case of BoM explosion? Anyhow you need to get the data into a memory/temp/internal table and loop through again-and-again till you hit the last branch (graph traversal). Additionally there can be loops in the graph (maybe it's not the case with BoM) which also need to be handled.

It's of course not a performant approach :(, but let me know when having better idea on HANA (on mass data), I'd also be intereseted in (applying parallel execution?).

Best regards,

Tamás

[Former Member · Accepted solution]

Can you not use a parent-child hierarchy in an Attribute View? This should traverse the graph for you.

[former_member399057 · Accepted solution]

Thx for the hint, I'll check out how it works! Just opened up HANA DEV Guide SPS 07 5.3.2 -> "An example of a parent-child hierarchy is a bill of materials hierarchy (parent and child) or an employee master (employee and manager) hierarchy."

@Krishna: as of the above maybe you can also have a trial how you can exploit from this feature for your use case

[Former Member · Accepted solution]

Nice spot. Here is the link to the HANA Developer Guide and it is on Page 323.

Remember that there is no recursive algorithm that can't be solved with iteration, and indeed most iterative algorithms can be replaced by set processing.

The HANA parent-child hierarchy is a good example of how HANA provides a set processing function to solve a problem using parallel processing that would be solved by a single-thread iterative or recursive algorithm in other systems.

Note that it is parallel because the hierarchy is stored as a tree - in HANA as a CPB+ tree index. This requires node traversal, which once a node branches, can be parallelized into as many threads as required, or as there are branches.

[Former Member · Accepted solution]

Hello Krishna,

I am curious to know if you have found the solution and what approach did you to take to write the procedure in SQLScript. We also have a similar requirement in the project.

Thanks,

Aamod

[former_member399057]

Hi Krishna,

as an alternative you can realize a loop (refactoring your recursive logic) within your script (either in a procedure or in a script based view).

Best regards,

Tamás

[Former Member]

Hi Tamas,

Thank you for your valuable information. So it is not possible to create a recursive procedure directly in HANA.

I will try creating a procedure with loop.

Best Regards,

Krishna.