Introduction
When implementing HANA models or CDS views, it is often required to calculate total values of multiple measures in each record and aggregate total values for specific record range. The question is whether we should add measures’ values first and then aggregate them or aggregate component measures first and then total their aggregated values.
Choosing one way or other could make a big difference in the performance of HANA model or CDS view.
This document presents several CDS views that show difference in performance for the above 2 approaches.
The discussed CDS views are adding and aggregating or aggregating and adding 10 measures to expenses and revenue values in custom table containing over 1 million records.
Custom Table used in CDS Views
The following custom table was used in presented CDS views:
@EndUserText.label : 'A Document Item Table'
@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #LIMITED
define table zaba_adoc {
@EndUserText.label : 'Document Number'
key docnum : abap.char(10) not null;
@EndUserText.label : 'Item Number'
key docln : abap.numc(4) not null;
doc_date : budat;
@EndUserText.label : 'Company Id'
company : abap.char(10);
@EndUserText.label : 'Region'
region : abap.char(10);
@EndUserText.label : 'Store Id'
store : abap.char(10);
@EndUserText.label : 'Property Tax'
prop_tax : abap.dec(15,2);
@EndUserText.label : 'Adertising'
advertising : abap.dec(15,2);
@EndUserText.label : 'Administration'
admin : abap.dec(15,2);
@EndUserText.label : 'Payrol'
payrol : abap.dec(15,2);
@EndUserText.label : 'Utilities'
utilities : abap.dec(15,2);
@EndUserText.label : 'Insurance'
insurance : abap.dec(15,2);
@EndUserText.label : 'Sales'
sales : abap.dec(15,2);
@EndUserText.label : 'Rent'
rent : abap.dec(15,2);
@EndUserText.label : 'Royalties'
royalties : abap.dec(15,2);
@EndUserText.label : 'Frenchise'
frenchise : abap.dec(15,2);
}
The above table contains over 1 million records. Some table records are shown on the following screen:
CDS Views
The following CDS Views were implemented:
The aggregate and add approach was implemented in the following CDS views:
◉ ZABA_AAVAA_10AGGREGATE_DDL
◉ ZABA_AAVAA_20ADD_DDL
The add and aggregate approach was implemented in the following CDS views:
◉ ZABA_AAVAA_10ADD_DDL
◉ ZABA_AAVAA_20 AGGREGATE_DDL
Aggregate and Add Approach
The following CDS views were developed to implement Aggregate and Add Approach:
@AbapCatalog.sqlViewName: 'ZAVA10AGRDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregate Measures First'
define view ZABA_AAVAA_10AGGREGATE_DDL
with parameters
p_dayfr : char8,
p_dayto : char8
as select from zaba_adoc as _adoc
{
key docln,
count(*) as nrecs,
sum(_adoc.prop_tax) as prop_tax,
sum(_adoc.advertising) as advertising,
sum(_adoc.admin) as admin,
sum(_adoc.payrol) as payrol,
sum(_adoc.utilities) as utilities,
sum(_adoc.insurance) as insurance,
sum(_adoc.sales) as sales,
sum(_adoc.rent) as rent,
sum(_adoc.royalties) as royalties,
sum(_adoc.frenchise) as frenchise
}
where _adoc.doc_date between :p_dayfr and :p_dayto
group by
docln
_____________________________________________________________
@AbapCatalog.sqlViewName: 'ZAVA20ADDDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Add Meassures after Aggregation'
define view ZABA_AAVAA_20ADD_DDL
with parameters
p_dayfr : char8,
p_dayto : char8
as select from ZABA_AAVAA_10AGGREGATE_DDL( p_dayfr:$parameters.p_dayfr,
p_dayto:$parameters.p_dayto )
{
docln,
:p_dayfr as day_from,
:p_dayto as day_to,
nrecs,
prop_tax + advertising + admin + payrol + utilities + insurance as expenses,
sales + rent + royalties + frenchise as revenue
}
When executing ZABA_AAVAA_20ADD_DDL CDS view, the following results were produced in 28 ms.
The ZABA_AAVAA_20ADD_DDL CDS view was run 10 times with the following execution times:
◉ 28 ms
◉ 23 ms
◉ 23 ms
◉ 20 ms
◉ 24 ms
◉ 30 ms
◉ 23 ms
◉ 25 ms
◉ 19 ms
◉ 24 ms
The average execution time of 10 runs is 23.9 ms
Add and Aggregate Approach
The following CDS views were developed to implement Add and Aggregate Approach:
@AbapCatalog.sqlViewName: 'ZAVA10ADDDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Add Measures First'
define view ZABA_AAVAA_10ADD_DDL
with parameters
p_dayfr : char8,
p_dayto : char8
as select from zaba_adoc as _adoc
{
key docln,
prop_tax + advertising + admin + payrol + utilities + insurance as expenses,
sales + rent + royalties + frenchise as revenue
}
where _adoc.doc_date between :p_dayfr and :p_dayto
______________________________________________________________
@AbapCatalog.sqlViewName: 'ZAVA20AGRDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Aggregate Measures after They were Added'
define view ZABA_AAVAA_20AGGREGATE_DDL
with parameters
p_dayfr : char8,
p_dayto : char8
as select from ZABA_AAVAA_10ADD_DDL( p_dayfr:$parameters.p_dayfr, p_dayto:$parameters.p_dayto )
{
key docln,
:p_dayfr as day_fr,
:p_dayto as day_to,
count(*) as nrecs,
sum(expenses) as expenses,
sum(revenue) as revenue
}
group by
docln
When executing ZABA_AAVAA_20AGGREGATE_DDL CDS view, the following results were produced in 82 ms.
The ZABA_AAVAA_20AGGREGATE_DDL CDS view was run 10 times with the following execution times:
◉ 82 ms
◉ 99 ms
◉ 93 ms
◉ 88 ms
◉ 69 ms
◉ 96 ms
◉ 78 ms
◉ 97 ms
◉ 81 ms
◉ 68 ms
The average execution time of 10 runs is 85.1 ms
No comments:
Post a Comment