CDS Table Function using ‘WITH’ Clause – Improved response time

Introduction –

In this blog, you are going to see how performance can be improved using ‘WITH’ Clause in CDS Table function when you have multiple selects.

I will use below Problem Statement as an example and try same solution in 3 ways.

Problem Statement – Need to find the last person who changed CU Order Basic Start Date and when (i.e. ‘Changed By’ and ‘Changed on’ for Field AFKO-GSTRP).

Expected Result – 

Solution –

First Read table CDHDR to get only latest change against AFKO-GSTRP

Then pass CDHDR-CHANGENR to CDPOS-CHANGER along with other keys in order to get User id and changed on.

Let’s begin –

OPTION 1: Using Multiple CDS Views

Step 1: Get the latest change on AFKO-GSTRP

Step 2: Get Change By (cdhdr-username) and changed on(cdhdr-udate / utime).

Result 1 - 1345ms for 100 records

OPTION 2: CDS Table Function

Here I have used 2 selects, 1st as highlighted to get latest ‘CHANGENR’ on AFKO-GSTRP and then get CDHDR-USERNAME and CDHDR-UDATE/UTIME.

Result 2: 883ms for 100 records

OPTION 3: CDS Table Function using ‘WITH’ Clause

Now here if you compare with Option 2, 1st select statement is now moved out using ‘WITH’ clause.

WITH Clause – stores the data in a buffer or temporary table (just like ABAP Internal Table) which boosts the performance.

Result 3: 316ms for 100 records

Code for Option 3:

1.CDS Table Function –

@EndUserText.label: 'CDHDR Logs'

define table function ZTF_CDHDR_LOGS

returns {

  key client : abap.clnt;

  key aufnr: aufnr;

  username: cdusername;

  FNAME: fieldname;

  UpdateDate: abap.dats;

  UpdateTime: abap.tims;

  

}

implemented by method zclget_cdhdr_logs=>zget_cdhdr_logs;

2. Class –

class ZCLGET_CDHDR_LOGS definition

  public

  final

  create public .

public section.

interfaces if_amdp_marker_hdb.

class-methods zget_cdhdr_logs for table function ZTF_CDHDR_LOGS.

protected section.

private section.

ENDCLASS.

CLASS ZCLGET_CDHDR_LOGS IMPLEMENTATION.

method zget_cdhdr_logs

       by database function for hdb

       language sqlscript

       options read-only

       using cdhdr cdpos.

return

        with pos as ( select max( changenr ) as chnr, FNAME from cdpos

                                            where objectclas = 'ORDER' and

                                                  tabname = 'AFKO' and

                                                  fname = 'GSTRP'

                                            group by objectid, FNAME)

       select

                  hdr.mandant as client,

                  substring(hdr.objectid,6,12) as aufnr,

                  hdr.username,

                  pos.FNAME,

                  hdr.udate as UpdateDate,

                  hdr.utime as UpdateTime

                from cdhdr as hdr , pos

                    where hdr.objectclas = 'ORDER' and

                          hdr.changenr = pos.chnr

                         ;

                endmethod.

ENDCLASS.

Conclusion:

Below are the performance results and this is just an example as I have not compared like to like records but still i can see improved response time on CDS Table Function using ‘WITH’ Clause.