Skip to Content
Technical Articles
Author's profile photo Anversha S

ABAP Core Data Service(CDS) & SQL functions – Sample

ABAP CDS is not meant to be a query runtime performance improvement. Instead, it’s about how you develop and deploy your application. For example you can use CDS approach in case of ODATA consumption.

Requirement : Obtain material master details through 2 different methods

Traditional ABAP Code and through ABAP Core Data Services (CDS) with SQL functions.

Approach 1: Traditional ABAP Code approach

In Traditional ABAP Code approach we are joining above 2 tables and bringing the data to application layer. Then we will loop through above obtained result table and remove the preceding zero’s using standard function module.

report ytest_mara_cds.
tables: mara, makt.
types : begin of ty_mara,
          matnr type matnr,
          maktx type makt-maktx,
        end of ty_mara.
data : it_mara type table of ty_mara,
       wa_mara type ty_mara.
select mr~matnr
       mk~maktx from mara as mr
       inner join makt as mk
                 on mr~matnr = mk~matnr
       into table it_mara
       where mk~spras = 'E'.
loop at it_mara into wa_mara.
  call function 'CONVERSION_EXIT_ALPHA_OUTPUT'
    exporting
      input         = wa_mara-matnr
   IMPORTING
     OUTPUT        = wa_mara-matnr
            .
  modify it_mara from wa_mara.
  clear wa_mara.
endloop.

Approach 2 :Code the same requirement using ABAP Core Data Services(CDS). Here we are pushing the data and using the SQL function LTRIM. This SQL function will improve the performance.

@AbapCatalog.sqlViewName: 'ZXSHIMAT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Material View'
define view ZXSH_I_MATERIAL
as select from mara
association [0..1] to makt as _makt
    on mara.matnr = _makt.matnr {
key   LTRIM( matnr, '0') as Material_Code,
    mtart as Material_Type
}
where
_makt.spras = 'E'

Call this CDS view in ABAP.

repot ytest_mara_cds

select * from ZXSHIMAT into table @data(it_mara).

Conclusion :

We were able to push down the function module approach in scenario 1 to scenario 2. Similar way we can push most of the application layer logic to database layer through ABAP CDS.

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Anversha s,

       

      Should we just compare the Data Access time?

      this is directly related to CDS performance and other ways.

       

       

      Author's profile photo Jose Muñoz Herrera
      Jose Muñoz Herrera

      Hi,

       

      Based on answer from Horst Keller  on that blog https://answers.sap.com/questions/292725/cds-view-vs-abap-open-sql-performance.html I think that your analysis is wrong.

       

      Regards

      Author's profile photo Michael Biber
      Michael Biber

      I think he mixes 2 things in here which amplifies his results:

      1. Selection of data
      2. Postprocessing

      In CDS both can be done at once while you need a loop with OenSQL only. But even if you compare data access (internal and external) in his screenshots, CDS seems to be a bit quicker (though less dramatic).

      We also have to see the fact that he only selects two columns, at least one of them beeing key. Running this on HANA can be totally different than running this on a different DB.

       

      Best regards

      Michael BIber

      Author's profile photo Anversha S
      Anversha S
      Blog Post Author

      Hello Michael,

      Thanks for your feedback. My landscape is ECC on HANA. I was trying to explain the performance of code push down technique using CDS with SQL function. If the requirement is only for selecting the material data without any post processing there will not be any major performance difference.

      Regards,

      Anversha

       

      Author's profile photo Jacques Nomssi Nzali
      Jacques Nomssi Nzali

      Hello Anversha,

      in a 7.5 system, you could write:

      REPORT zz_test_opensql.
      START-OF-SELECTION.
      SELECT LTRIM( mr~matnr, '0' ), mk~maktx FROM mara AS mr
        INNER JOIN makt AS mk ON mr~matnr = mk~matnr
        UP TO 100 ROWS
        INTO TABLE @DATA(lt_mara)
        WHERE mk~spras = 'E'.

      This would be a better Open SQL vs. CDS test.

      JNN

      Author's profile photo Anversha S
      Anversha S
      Blog Post Author

       

      Hello Jacques,

      I am in AS 7.4 system, That’s y took this case. Incase of 7.5 system we can use open SQL without CDS. There will not be any major performance difference as both are database specific.

      Thank You

      Author's profile photo Shai Sinai
      Shai Sinai

      CDS isn't faster than OPEN SQL (at the end of the day, both implements exactly the same SQL).

      The only difference is that CDS is more flexible. Hence, allows you to implement more complex selections (and push logic to DB layer).

      Author's profile photo Anversha S
      Anversha S
      Blog Post Author

      Hello Shai,

      If you see approach 2, we have created a CDS view with SQL function. In application layer we just consumed this CDS view. We can save the post processing time fi we can utilize the SQL functions.

      Hope this clarifies you. Again incase of AS 7.5 system we can use Open Sql for the same CDS functionality. There will not be any major performance difference since both implement same SQL.

      Regards

      Anversha

       

      Author's profile photo Shai Sinai
      Shai Sinai

      If the comparison is Open SQL + ABAP vs. CDS, your claim is correct, of course

      (If it wasn't the case, the whole concept of database pushdown would make no sense).

       

      Author's profile photo Anversha S
      Anversha S
      Blog Post Author

      Hello Shai,

      This is a comparison/performance test of CDS view with SQL function vs ABAP Open SQL (below AS 7.5).

      Regards

      Anversha

       

      Author's profile photo Sreehari V Pillai
      Sreehari V Pillai

      I think this example is not good enough for performance comparison . We , here are comparing the performance of application layer ( which is your looping and conversion exit ) with database performance ( of a join with builtin functions ). These are not comparable at all .

       

      In the context of logic pushdown to database layer , may be we should take a data intense scenario and arrive at a result of comparison .

       

      Sreehari

       

       

       

      Author's profile photo Anversha S
      Anversha S
      Blog Post Author

      Performance comparison has been removed as its not possible to compare the execution time in application layer. Thanks for highlighting the same. A good example will be a normal application layer logic with AMDP approach.