Skip to Content

SAP HANA | With Clause performance

We are using SAP HANA 1.0 SPS12.

We have daywise table like below -

select trans_date,article,measure1,measure2 from table_1

Volume of table ~ 5 millions rows

we need to see data like -

Performance wise is it better to use WITH CLAUSE and hold data for one year and then split according to timeframe? OR Is it better to use separate aggregation for each timeframe as shown above.

As far as I understand, in RDBMSs like Oracle, WITH CLAUSE materializes results and use it from the memory. SAP HANA is In Memory database itself. Does using WITH CLAUSE in SAP HANA gives distinctive performance edge?

select 'day-1',sum(measure1),sum(meaure2) from table1 where trans_date=add_days(current_date,-1) group by 'day-1'
union all
select 'day-2',sum(measure1),sum(meaure2) from table1 where trans_date=add_days(current_date,-2) group by 'day-2' 
union all
select 'WTD',sum(measure1),sum(meaure2) from table1 where trans_date=add_days(current_date,-7) group by 'WTD'
union all
select 'WTD-1',sum(measure1),sum(meaure2) from table1 where trans_date=add_days(current_date,-15) group by 'WTD-1'
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers