CODEWITHSUNDEEP
joincachinghana
Jonas M.
Jonas M.

Reputation: 13

How to use result cache while using joins in SAP HANA?

SELECT
    cntdpts."PROJECT_SID",
    cntdpts."USER_SID",
    "CNTDPTS",
    "CNTQUERIES"
    FROM (
    SELECT
        "PROJECT_SID",
        "USER_SID",
        COUNT("DATA_POINT_SID") AS "CNTDPTS"
    FROM
        CNTDPTS
    GROUP BY
        "PROJECT_SID",
        "USER_SID" WITH HINT(RESULT_CACHE) ) cntdpts
INNER JOIN (
    SELECT
        "PROJECT_SID",
        "USER_SID",
        COUNT("QUERY_SID") AS "CNTQUERIES"
    FROM
        CNTQUERIES
    GROUP BY
        "PROJECT_SID",
        "USER_SID" WITH HINT(RESULT_CACHE) ) cntqueries ON
    cntdpts."PROJECT_SID" = cntqueries."PROJECT_SID"
    AND cntdpts."USER_SID" = cntqueries."USER_SID" WITH HINT(RESULT_CACHE)

I am having troubles with using cached table functions. If I run the two subqueries "cntdpts" and "cntqueries" individually they return the result within <100ms (because they use the cache of the table function CNTDPTS and CNTQUERIES. However if I run the full query with joining the two subqueries it takes >5s and HANA does not seem to take advantage of the cached results from the subqueries. Is there any HINT I still need to add maybe?

Upvotes: 1

Views: 718

Answers (1)

Niels
Niels

Reputation: 1

You will need to add WITH HINT(RESULT_CACHE_NON_TRANSACTIONAL) to your outermost query.

See also https://help.sap.com/viewer/9de0171a6027400bb3b9bee385222eff/2.0.05/en-US/3ad0e93de0aa408e9238fa862e4780df.html

Upvotes: 0

Related Questions