Thursday 23 January 2020

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

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:

SAP ABAP Tutorial and Material, SAP ABAP Guides, SAP ABAP Learning, SAP ABAP Certification, SAP ABAP Online Exam, SAP ABAP Prep

CDS Views


The following CDS Views were implemented:

SAP ABAP Tutorial and Material, SAP ABAP Guides, SAP ABAP Learning, SAP ABAP Certification, SAP ABAP Online Exam, SAP ABAP Prep

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.

SAP ABAP Tutorial and Material, SAP ABAP Guides, SAP ABAP Learning, SAP ABAP Certification, SAP ABAP Online Exam, SAP ABAP Prep

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.

SAP ABAP Tutorial and Material, SAP ABAP Guides, SAP ABAP Learning, SAP ABAP Certification, SAP ABAP Online Exam, SAP ABAP Prep

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