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.

Try it out and feel free to ask any questions.

Happy learning!!

Cheers..

Sudeep