Skip to Content
Technical Articles
Author's profile photo Sudeep Basak

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

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      Whenever I read about a clever way to "write" SQL to make it execute faster, I'm interested!

      So, this is a very interesting blog post for me.

      Sadly, it's not very good.

      The claim, that using the common table expression (WITH clause) would "store data in a buffer table" is straight out wrong.

      Some DBMS (e.g. Postgresql) indeed have/had an optimizer that would materialize CTEs, but HANA does not do this.

      This also means the explanation for why the second statement is quicker is wrong too.

      Let's look at the original statement:

      The sub-select to find the maximum change number uses four filters:

      1. objectclas = 'ORDER'
      2. tabname ='AFKO'
      3. fname = 'GSTRP'
      4. OBJECTID=hdr.objectid

      In the CTE only three filters are present:

      1. objectclas = 'ORDER'
      2. tabname = 'AFKO'
      3. fname = 'GSTRP'

      Including OBJECTID into the GROUPING clause but not into the projection prevents that the CTE can be joined at the same granularity via object_id.

      The effect is that HANA has to join far fewer different changenr/objectid combinations - that makes the join a lot faster but also makes the result wrong.

      So, both the claimed performance effect and the explanation of how it is supposed to work are wrong in this case.

      A check of what HANA actually does with the changed query with EXPLAIN PLAN or PLANVIZ would have shown that the theory about what happens here does not actually check out.

      One contributing factor might be the unfortunate use of non-ANSI-join syntax.

      Make your life a bit easier by writing out INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, or CROSS JOIN with an ON specification.

      Don't mix it up with the WHERE clause. As this example shows, it's easy to get lost when everything is folded into the same clauses and this ends up with wrong results.

      As if that was not enough, this whole exercise of pulling the SQL statement with the CTE into a table function took away the opportunity to think about how else the requirement (only show most recent change date and user) could be implemented.
      Depending on the usage scenario, avoiding the "join against the MAX" and using a window_function (RANK or ROW_NUMBER) could lead to better response times.

      Sorry if this comment comes across harsh - it's not meant as a personal critique. But as the results are wrong and the understanding is false, knowing about this should serve you better than an unreflected "nice blog!'.