HANA Inner Join Optimization

[former_member198121]

We have a aggregated view which runs on a table which is HASH, Range Partitioned on two columns

Case 1:

SELECT

"Thing" AS THING_ID,

"PropertyValue",

"PropertySetTypeId",

"PropertyId"

FROM

"READINGS_CURRENT" AS RC

where

RC."PropertySetTypeId" in ('healthState','pca') and

RC."PropertyId" in ('SCORE','STATUS')

The above code provides the output under 2 Seconds.

Case 2:

Instead of using in if inner join is used, it takes 1:15:00 (>1 Minute)

WITH RES_SPLIT_CON_PST AS

(

SELECT 'healthState' AS PST, 'SCORE' as PT FROM DUMMY

UNION ALL

SELECT ‘pca’ AS PST,'STATUS' as PT FROM DUMMY)

SELECT

"Thing" AS THING_ID,

"PropertyValue",

"PropertySetTypeId",

"PropertyId"

FROM

"READINGS_CURRENT" AS RC

INNER JOIN

RES_SPLIT_CON_PST AS RP

ON

RC."PropertySetTypeId" = RP.PST and

RC."PropertyId" = RP.PT;

Case 3:

Or if we use Select Query within IN clause, that also takes around 1:15:00 (>1 Minute)

WITH RES_SPLIT_CON_PST AS

(

SELECT 'healthState' AS PST, 'SCORE' as PT FROM DUMMY

UNION ALL

SELECT ‘pca’ AS PST,'STATUS' as PT FROM DUMMY)

SELECT

"Thing" AS THING_ID,

"PropertyValue",

"PropertySetTypeId",

"PropertyId"

FROM

"READINGS_CURRENT" AS RC

where

RC."PropertySetTypeId" in (select PST from RES_SPLIT_CON_PST ) and

RC."PropertyId" in (select PT from RES_SPLIT_CON_PST)

May I know is it a problem with how HANA handles the data or is there any problem with query itself?