Why do Loops work extremely slow in HANA?
I created a procedure to compare 2 hierarchies. I used the hierarchy functions, and put all the functions outside the loops to speed the loop processing. Up till before the loop begins, the processing is pretty fast.
I have tried to optimise the loop as much as possible. I am looping only on the leaf nodes not present in either of the hierarchies by finding a complement.
Since I need to assign colors to all the ancestors of the missing/additional leaf nodes, i inserted a nested while loop, which is only traversed once, and in the subsequent times it encounters a flag and breaks out f the nested loop.
The only selects inside the loops are on table variables with results of the hierarchy, wherein 1 select in the main for loop contains the results of the hierarchy function with as many records as the union result, and the another select within the nested while loop contains only the level 2 nodes (root nodes for our purposes, to contain the aggregation results), such that for 80k records, there will be less than 50 records in the table variable.
For a hierarchy with a union result of 80k records (approx), where 30k (approx) activity nodes are different, and hence 30k iterations of the main loop, the performance is miserable (tried on CF, and also tried calling from a JAVA code). It runs endlessly for hours, despite all the possible optimisations from my end.
I am unable to understand why this code is giving such a miserable performance, when a similar code in ABAP would be much faster.
Attaching the code, it needs an addition on the loop (on untraversed lt_descendants nodes) with not more than 40-50 iterations for 80k records. I have not added it yet, because it will have a negligible performance impact compared to the current run.
Can anyone please shed light on why it takes such a loooong time?
Also, I believe the same code is possible through subqueries too, in a single query, which I am trying now as an alternative. If possible please shed some light on the same too.