Skip to Content
0
Jul 01, 2020 at 09:52 AM

HANA Table Function slow onced used within a tiny join

128 Views

Hi, once I use a table function in a join it gets very slow. If I run the individual queries 1 and 2 they need <1s. However the full query needs >10s, even though the individual queries only return 140 rows!

SELECT
 *
FROM
 (
 -----------------QUERY 1 ------------
 SELECT
  "PROJECT_SID",
  "SITE_SID",
  COUNT("DATA_POINT_SID") AS "CNT_DATA_POINTS"
 FROM
  CNT_DPTS ('9999-12-01')
 WHERE
  "PROJECT_SID" = '1146AE1E18534ECAC81F99FAB0688B9D'
 GROUP BY
  "PROJECT_SID",
  "SITE_SID"
  -----------------
) cntdpts
INNER JOIN (
 -----------------QUERY 2 ------------
 SELECT
  "PROJECT_SID",
  "SITE_SID",
  COUNT("TYPE_ID") AS "CNT_PDS"
 FROM
  CNT_PDS ('9999-12-01',
  3)
 WHERE
  "PROJECT_SID" = '1146AE1E18534ECAC81F99FAB0688B9D'
 GROUP BY
  "PROJECT_SID",
  "SITE_SID"
  -----------------  
) cntqueries ON
 cntdpts."PROJECT_SID" = cntqueries."PROJECT_SID"
 AND cntdpts."SITE_SID" = cntqueries."SITE_SID"
 --WITH HINT(RESULT_CACHE)