Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

horrible sql execution plan lead to terrible performance

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.

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question