horrible sql execution plan lead to terrible performance

[Former Member]

I have a sql running against HANA rev85, in short this SQL joins 2 sub-query and create a result. each sub-query is a standalone aggregation. I expect the sub-query take place first then the join afterwards, so the small amount records are used to do the join, however the SqlViz Plan shows otherwize. It first join the physical table with some filter in place, then the aggregation at last, this lead a bad sql performance up to 1.5 hrs and only output 1.6 million records.

However, if i materialize the sub-query into a physical table at the first, then join it with fact table at last the execution time is only 3 seconds.

the sql statement , sql execution plan and viz plan file has been attached.

here are some screen shoot and analysis.

my question is, is there a way to force sql execution plan to execute sub-query first?

/*
CREATE COLUMN TABLE ZNMAT888 (CUSTMNO VARCHAR(50), TPFABLITY DECIMAL(20,5),  NPFABLITY DECIMAL(20,5))


INSERT INTO ZNMAT888(
SELECT CUSTMNO, SUM(PFABLITY) AS TPFABLITY,
  COUNT(PFABLITY) AS NPFABLITY
  FROM ZNMAT004  AS TAB
  WHERE TAB.PERIO = '2014007'
  AND TAB.ACNTAGE IN ( SELECT ORGANLEAF FROM ZNMAT046 WHERE ORGANTYP = '602' AND ORGANCOD = '0200' )
  AND TAB.DATYPE = 'B0'
  GROUP BY CUSTMNO
  )


*/


SELECT '0200' AS ACNTAGE2 ,
  '' AS OPTNAGE2 ,
  'FTYZ0000000003' AS FACTORYID ,
  'PFABLITY' AS FTYNAM_RL ,
  '100' AS FACTORYWET ,
  --(CASE SUM(TPFABLITY) WHEN 0 THEN '' ELSE 'PFABLITY'  END) AS FTYNAM_ST,
  TAB.KEYVALUE,


  MANDT,
  '2014007'  AS PERIO ,
    'FH_OTHER_GG' AS RULEID ,
    TAB.LOBID_MAPRD,
    TAB.LOBID_MACUS,
  TAB.TAMBOAIAO,TAB.PFABLITY,
  TAB.ACNTAGE,TAB.OPTNAGE,
  TAB.ABJGTLINES,
  TAB.MAKTPRO,
  TAB.COBASEDPRO,
  TAB.CUSTMNO,
  TAB.STAFFNO ,
  "GENERALACNT" ,

  CASE  SUM(TPFABLITY) WHEN 0 THEN 'X' ELSE '' END AS ZIFNULL,
  ROUND(1/SUM(CUSTMNUM)*(CASE SUM(TPFABLITY) WHEN 0 THEN 1/SUM(NPFABLITY)
  ELSE SUM(PFABLITY)/SUM(TPFABLITY) END),14) AS ZSCALE

  FROM ZNMAT004 AS TAB , --ZNMAT888 AS YINZI3,

  ( SELECT COUNT(DISTINCT CUSTMNO) AS CUSTMNUM
  FROM ZNMAT004  AS TAB
  WHERE TAB.PERIO = '2014007' AND
  TAB.ACNTAGE IN ( SELECT ORGANLEAF FROM ZNMAT046 WHERE ORGANTYP = '602' AND ORGANCOD = '0200' )
    AND TAB.DATYPE = 'B0' ) AS YINZI,

    ( SELECT CUSTMNO, SUM(PFABLITY) AS TPFABLITY,
  COUNT(PFABLITY) AS NPFABLITY
  FROM ZNMAT004  AS TAB
  WHERE TAB.PERIO = '2014007'
  AND TAB.ACNTAGE IN ( SELECT ORGANLEAF FROM ZNMAT046 WHERE ORGANTYP = '602' AND ORGANCOD = '0200' )
  AND TAB.DATYPE = 'B0'
  GROUP BY CUSTMNO
  ) AS YINZI3


  WHERE
  TAB.CUSTMNO = YINZI3.CUSTMNO  AND
  TAB.PERIO = '2014007'
    AND TAB.ACNTAGE IN ( SELECT ORGANLEAF FROM ZNMAT046 WHERE ORGANTYP = '602' AND ORGANCOD = '0200' )
  AND TAB.DATYPE = 'B0'



  GROUP BY
  MANDT,
  KEYVALUE,
  TAB.LOBID_MAPRD,
  TAB.LOBID_MACUS,
  TAB.TAMBOAIAO,
  TAB.PFABLITY, TAB.ACNTAGE, TAB.OPTNAGE,
  TAB.ABJGTLINES, TAB.MAKTPRO,TAB.COBASEDPRO,TAB.CUSTMNO,
  TAB.STAFFNO,
  "GENERALACNT"
    HAVING ROUND( 1/SUM(CUSTMNUM)*(CASE SUM(TPFABLITY) WHEN 0 THEN 1/SUM(NPFABLITY)
    ELSE SUM(PFABLITY)/SUM(TPFABLITY) END),14) > 0

SQL execution plan, shows all execution is within Column Engine

vizplan shows that join happens before aggregation.

[lbreddemann]

While the PlanViz shows the joins feed into the aggregation, it doesn't show that this happens before the aggregation is done.

Also, in the graph you posted, you see that only very few records get out from the join.

Did you actually execute the query in PlanViz or is this the initial display that you get, when you open PlanViz?

The query itself while syntactically correct yields a (partial) cartesian join between ZNMAT004 and the subqueries YINZI3 and YINZI with just ZNMAT004.CUSTMNO = YINZI3.CUSTMNO a the join condition.

Besides that this looks 'odd' YINZI3 columns are not referenced anywhere else in the query.

So, what does it do here?

As with many query performance issues, a good start is to make sure that it's clear what the query should do and that it is expressed clearly.

- Lars

[Former Member]

It's interesting that you called out the partial cartesian join as an issue - since our badly performing queries I mentioned also generate partial cartesian joins. In our case, it is on purpose to generate additional data - for instance, we plan at a year-level and want to blow it down to the month; or plan at a product category level and blow it down to individual products for reporting. In our case the join order HANA chooses results in gigantic intermediate result sets (e.g. 1000x or more the final result), which results in bad performance. In these cases it would be nice to be able to tell HANA which order to do things in via a hint or some other construct - but in the meantime, local temporary tables are a decent workaround, it just requires using SQLScript for part of your ETL process.

[lbreddemann]

Well, for the case presented by the OT the cartesian join does not look sensible. So it at least requires an explanation why this should be like this.

Combining tables based on no common field or condition (the cross join/cartesian join) is something that does not make sense in a normalized data model.

Using cartesian joins to blow up result sets is a technique that does have it's limitation. For use cases like disaggregation - like the one you mentioned - SAP HANAs planning engine uses more efficient algorithms than incorporating it into the SQL statement.

Similarly for time series, there are procedures available for disaggregation of time series data.

Hints, and this is my personal opinion, are often workarounds that should help out when the original problem is located somewhere else (e.g. misunderstanding of the data, the way SQL works, or what the used DB engine does with queries). And when applied, those hints are often poorly understood and cannot later on be managed properly (e.g. when the data or the DB engine changes).

Coming back to your case I think it does not make sense to mix up your situation and the one presented by the OP. So, rather open a thread on your own, providing the information required to understand what's happening in your case.

- Lars

[Former Member]

Hi Martin, did you ever figure out a way to do this? We have some similar badly-performing queries and have gone the route of creating local temporary tables with the sub-queries and then feeding those into a second query to load our final table. I was really hoping to find an equivalent of Oracle's MATERIALIZE hint, but haven't found anything yet.

[Former Member]

some update: on the vizplan aggregation node. it shows: Reason for no column search: Unsupported operator order: CS_JOIN over CS_AGGREGATION. Unsupported join condition (Cartesian product)