Too many tables in SQLScript?

[Former Member]

Hi Folks,

Curious if anyone else has ever seen the following error when trying to activate a rather large stored procedure?

Could not execute 'CALL N67250.TEST_PROC' in 521 ms 768 µs .

SAP DBTech JDBC: [463]: number of tables exceeds its maximum:  [463] N67250.TEST_PROC: line 5 col 2 (at pos 39): [463] (range 3) number of tables exceeds its maximum exception: number of tables exceeds its maximum: 4095: line 1176 col 620 (at pos 600669)Please check lines: 

I currently have access to two machines, both Rev67, and I'm getting the error on both.

Attached is a "simplified" version of my procedure (apologies for the lengthy DDLs, didn't care to spend time trimming out unnecessary fields). The won't make much sense in many cases because I cut out as much code as I could to the point where the error would disappear. Nonetheless you can run it, create the empty tables, create the procedure, and then run the procedure yourself - and you should be able to reproduce it.

My suspicion is that internally HANA builds big fat queries wherever it can as opposed to lots of intermediate result sets. One of these big fat queries is probably too big/fat.

Not quite sure the best solution is. I thought I could outsmart HANA by using some CE functions but that didn't help. What I'll likely do is build some of the logic as models where possible and see if that somehow helps...

Cheers,

Jody

too_many_tables.txt.zip

[Former Member · Accepted solution]

Honestly - you have a lot going on there :=)

My guess is that the procedure is causing the optimizer to recurse and create an infinite amount of tables. I assume it is an optimizer bug in you scenario.

Open an OSS message. Those guys can help fix this.

If you want to fix it yourself, I would recommend nesting the SQLScript procedures. This will probably cause the optimizer bug not to manifest. It doesn't surprise me too much that the CE function gives you the same problem - it may be hitting the same optimizer bug.

John

[Former Member · Accepted solution]

Thanks, John. Yes it is a bit of a beast. And it wouldn't be the first time I caused an infinite recursion error...   (Learned the hard way to be careful with calculated column references, crashing the system in Rev 35 before activation would catch such cycles)

Holding off on the OSS message now but may very well have to go to the last resort.

In terms of of nesting procedures, I'm assuming you just mean splitting it up into separate procedures and making calls from one to the other(s)? I reckon it's worth a try. If I find anything useful, I'll update this post.

Cheers,

Jody

[Former Member · Accepted solution]

Yes exactly. Nesting forces an execution path which you define by the structure. If your structure got too complex and the optimizer makes a mistake then often nesting will fix this.

It's also the case if the optimizer doesn't parallelize your SQLScript procedure. Nesting can fix this if you write your code carefully.

I'm pretty confident this will fix it. But either way please raise an OSS message so they can fix the optimizer bug.

John

[Former Member · Accepted solution]

Thanks, John. I split the code provided above into two procedures which resolved the bug. Also, I was also able to apply additional refactoring/optimization to the production code in a single procedure and the bug has disappeared from there also.

I'm curious about forcing an execution path in SQLScript. I was always under the impression that there was little that could be done to force particular execution with declarative statements, even if the code was split up. Since read-only procs can only call other read-only procs, and assuming no imperative logic is used - my understanding is that HANA would basically build a single data-flow graph for the code and then optimize from there. Do you have any examples of how to demonstrate forcing particular execution via the nesting approach? I tried by exploring VizPlan results for a single proc and nested proc, but the plan graph was the same (for a simple example though).

I'll see what I can do about the OSS message. It's the client's basis team that handles those and they seem hesitant to raise messages for issues that aren't affecting development (i.e. since the bug disappeared when I refactored/optimized the code).

Give the reproducible code above, if you or anyone else would like to help with the OSS with less political maneuvering, that would of course be great as well.

[Former Member · Accepted solution]

Honestly, the SQLScript compiler is like most other black boxes - if an execution path is suboptimal or the optimizer messes things up, you can usually prod it with a stick. Often, nesting procedures is a good stick!

Yes for simple examples, it will optimize out nesting. For more complex examples it is often hard to find out, because PlanViz often fails for complex code.

Hopefully someone from the HANA dev team is reading this and cares to fix it!

John

[Former Member · Accepted solution]

Lol, nothing like a good prodding. The ol' stop and restart works from time to time as well. 

Thanks John.