HANA SQLScript Hanging

[former_member206725]

HANA Gurus,

We have been calling a SQLScript Procedure from an EndRoutine of a BW transformation. The End Routine triggers about 10 HANA SQLScript processes in paralel, each processing about 5,000 records.

It was running quite well for a while, but suddently (and I mean suddenly), it simply stopped working. The HANA procedure simply stops processing and  sits in an Optimizer step. After a while, the Optimizer simply stops, fills up the memory and then crashes with Out of Memory (so, the BW Data Transfer Package crashes, as the procedure fails).

The general flow of the procedure is like the one below (in pseudo-code):

-> procedure receives a table variable (STT) with about 5,000 records

--> LT_BASE  = SELECT FROM "a DSO active table (which contains about 1,700,000,000 records)"  as Table1

                        JOINS "a small master data table (20 records) " as Table2

                         ON     Primary Keys of Table2 (not primary keys of table 1)

                        JOIN :STT

                        ON  primary keys of Table1 = 2 columns of table :STT

                        -- NB I have tried both ways (table 1 join STT or STT join table 1)

                        -- this result set should be approximatelly 25 x 5,000 = 125,000 records

-->  SEL1 = SELECT a subset of :LT_BASE based on criteria 1

-->  SEL2 = SELECT a subset of :LT_BASE based on criteria 2

--> SEL3 =  SELECT a subset of :LT_BASE based on criteria 3

...

--> SEL10 =  SELECT a subset of :LT_BASE based on criteria 10

-> :RESULT_SET =

SELECT * form :LT_BASE

left outer join :SEL1 on Primary Key of Table 1

left outer join :SEL2 on Primary Key of Table 1

left outer join :SEL10 on Primary Key of Table 1

-> return :RESULT_SET

It was processing well to start with - but now it hangs (sometimes it runs to the end, but very slowly)

I have tried all possible ways with that procedure:

• using SQL (Select), using Calculation Engine functions (CE_xx) functions.
• reading tables directly
• Joins either way (putting the small table variable in front of the large table and vice-versa)
• Subqueries (SELECT from large table WHERE keys of large table IN (keys of table variable)
• using HANA views over the "Large Table Join Master Data Table" (started with attribute views, then went to Analytic views)
• having only one record in the initial table variable.

My thoughts:

• The Optimizer seems to be selecting between OLAP engine and Join Engine .. but this is sort of unpredictable (sometimes it works slowly)
• The Optimizer worked at a point in time, but stopped later - what changed?
• I run the same procedure in a system with smaller tables - the performance is acceptable, and the processing times very similar (the difference is the planning time)
• It looks like the statistics that the HANA Optimizer relies upon could be corrupted - any way to refresh them?
• The Large Table (a DSO Active table) is partitioned in three partitions only (one per node). The BW team is a bit reluctant to agree with further paritioning, directly via DDL - any issues? Would an SPO help?

My questions:

• Has anyone out there ever experienced this?
• Could it be a memory management problem present in our revision? (it is 69.3) and potentially resolved in a later revision?
• Could I restructure my code in a differnt way to avoid the problem? (I am happy to provide the source code via email)
• Is there a way that I can create hints for the optimizer, reload statistics or so on?

Gurus: Thomas Jung, Richard Heilman, John Appleby, Henrique Pinto, Lars Breddemann, Sagar Joshi, Do you have any suggestions as to where I should look?

Thanks

[lbreddemann · Accepted solution]

Hi Renato

This needs to be investigated further than what we can sensibly do here in the forums.

Please open a support incident with SAP support for that.

- Lars