Performance of Add & Aggregate vs Aggregate & Add in CDS Views

[former_member588394]

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

Conclusions

In the discussed case study on Aggregate and Add Approach versus Add and Aggregated Approach in CDS views, the Aggregate and Add Approach is over 3.56 times faster than Add and Aggregate Approach.

The case study was done on a very small table with only 1 million records and 10 measures and the execution times for 2 approaches are very short. Nevertheless, the 3.5 times difference is consistent and significant.

In tables with billions of records and hundreds of measures, the differences in execution times would be much greater; i.e., even more than 100 times. It could mean that instead of getting back results in; e.g., 1 second or less one could wait 30, 50 or even 100 seconds or more to get back the same results.

When implementing aggregation first, each column is aggregated in parallel process so there is no penalty on performance for multiple column aggregation. Once columns are aggregated, measures are added only in a single record to produce expenses and revenue measures.

When implementing calculation of expenses and revenue at the lower level, expenses and revenue have to be calculated in million records. Then only expenses and revenue measures are aggregated. The calculation of expenses and revenue in each record increases significantly the execution time.

For the performance improvement:

1. Pass parameters to the lowest level CDS view/HANA model to limit number of records selected


2. Aggregate selected records as soon as possible

The same rules apply to both CDS views and native HANA models.