Skip to Content
Technical Articles

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.

12 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • 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

       

  • 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

    •  

      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

  • 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).

    • 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

       

      • 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).

         

        • Hello Shai,

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

          Regards

          Anversha

           

  • 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

     

     

     

    • 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.