3330071 - Long running or hanging query compilation due to large number of correlated subqueries

Execution of a SQL statement that contains the following pattern is either hanging or taking a very long time:

SELECT
     DISTINCT <tab1>.<col1>,
     <tab1>.<col2>,
     <tab2>.<col3>,
     <tab2>.<col4>,
     <tab1>.<col5>,
     <tab3>.<col6>,
     <tab3>.<col7>,
     <tab3>.<col8>,
     (SELECT
     DISTINCT <col6> 
        FROM <schema>.<tab3> 
        WHERE <tab3>.<col2>=<tab2>.<col2> 
        AND <tab3>.<col6>=<tab2>.<col6> 
        GROUP BY <tab2>.<col3>,
     <tab3>.<col6>) AS <col2>_<col6> ,
     (SELECT
     DISTINCT <col6> 
        FROM <schema>.<tab3> 
        WHERE <tab3>.<col2>=<tab2>.<col3> 
        AND <tab3>.<col6>=<tab2>.<col6> 
        GROUP BY <tab2>.<col2>,
     <tab3>.<col6>) AS <col3>_<col6>
    ...
    FROM <schema>.<tab1> <tab1> JOIN <schema>.<tab2> <tab2> ON <tab1>.<col2> = <tab2>.<col2> JOIN <schema>.<tab3> <tab3> ON <tab1>.<col2> = <tab3>.<col2> ) 
WHERE ...

Such a statement might also be part of a view definition: The statement will then also be executed when the statement has to be recompiled, for example, when one of the dependent bases table is re-created. In such a case, the DDL statement for table (re)creation will take longer than expected. The more correlated subqueries are removed from the statement, the faster it will run.

On HANA side, the call stacks of the involved threads look like this:

...
ptime::qo_ImpliedGrouping::addGroupingImpliedByEqualComp
ptime::qo_ImpliedGrouping::addGroupingImpliedByEqualConj
ptime::qo_ImpliedGrouping::addGroupingImpliedByEqual
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
ptime::qo_ImpliedGrouping::collectInternalJoin
...

SAP HANA, platform edition 2.0

KBA , HAN-DB-PERF , SAP HANA Database Performance , Problem