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.
Hi Anversha s,
Should we just compare the Data Access time?
this is directly related to CDS performance and other ways.
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
I think he mixes 2 things in here which amplifies his results:
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:
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.