Skip to Content

How fast is ABAP Core Data Service (CDS) with SQL functions?  This is one of the frequent question raised by technical consultants after any ABAP CDS sessions. We can demonstrate speed of ABAP CDS with SQL functions through below simple approach, I have written same requirement in two different methodologies. Business requirement is to obtain the material number from MARA table and material description from MAKT table. Material number in final result should be without preceding zero’s.

We will approach above requirement through two 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.

Now run the transaction SAT to check the runtime analysis.

From above analysis , it took 174047 micro seconds which is equal to 0.17 sec.

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.

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 * from ZXSHIMAT INTO TABLE @it_mara.

Now run the transaction SAT to check the runtime analysis.

From above analysis , it took 87895 micro seconds which is equal to 0.088 sec.

Conclusion : ABAP CDS approach with SQL functions took only half of the traditional approach execution time. This is achieved because of the use of SQL function ‘LTRIM’ in ABAP CDS query. How ever if there is only simple select query without any material conversions, then performance will not have much difference in both cases.Query performance will improve by using SQL functions. This is also the case for any aggregations.

*These findings may vary depending on the system load, but not much.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

    1. Anversha s Post author

      Hello Jose,

      Could you please try this in your landscape and share the observation ? I am getting 2 different execution in both scenarios. My landscape is ECC on HANA. I was trying to explain the performance of code push down technique using CDS with SQL function.

      If there is no ‘SQL function’ in my CDS query , then there will not be much performance difference as you told. Also if your landscape is AS 7.5 and above you can use Open SQL in your query without going for the mentioned ABAP CDS view . In that case system will not show much performance difference.

      Thank You.

       

      (0) 
  1. 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

    (1) 
    1. Anversha s 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

       

      (0) 
  2. Jacques Nomssi

    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

    (5) 
    1. Anversha s 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

      (0) 
  3. 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).

    (2) 
    1. Anversha s 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

       

      (0) 
      1. 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).

         

        (0) 
        1. Anversha s 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

           

          (1) 
  4. 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

     

     

     

    (0) 

Leave a Reply