performance of calculation example (ABAP, Open SQL, CDS)
Dear community, I recently presented a calculation example with ABAP, Open SQL and ABAP CDS in this blog. It was about the different technologies that can be used to achieve the same result. I was surprised by the interest in the example. For the sake of completeness, I would now like to add a performance analysis. First, a few notes …
The primary key of the database table ZCALC has been set to NUMC10 as data element. This allowed more test data. With a report, I generated 2,000,000 test records.
I’ve adjusted the code of my example a little bit and implemented the respective solutions in methods.
REPORT zcalculation_example. CLASS lcl_calculation_example DEFINITION. PUBLIC SECTION. METHODS abap_based. METHODS open_sql_based. METHODS cds_based. ENDCLASS. CLASS lcl_calculation_example IMPLEMENTATION. METHOD abap_based. TYPES: BEGIN OF calculation, example TYPE numc10, summand_1 TYPE int4, summand_2 TYPE int4, addition_result TYPE int4, END OF calculation. TYPES calculations TYPE TABLE OF calculation WITH KEY example. DATA abap_based_result TYPE calculations. SELECT * FROM zcalc INTO CORRESPONDING FIELDS OF TABLE abap_based_result. LOOP AT abap_based_result ASSIGNING FIELD-SYMBOL(<row>). <row>-addition_result = <row>-summand_1 + <row>-summand_2. ENDLOOP. ENDMETHOD. METHOD open_sql_based. SELECT example, summand_1, summand_2, summand_1 + summand_2 AS addition_result FROM zcalc INTO TABLE @DATA(open_sql_based_result). ENDMETHOD. METHOD cds_based. SELECT * FROM zcdscalc INTO TABLE @DATA(cds_based_result). ENDMETHOD. ENDCLASS. START-OF-SELECTION. DATA(example) = NEW lcl_calculation_example( ). example->abap_based( ). example->open_sql_based( ). example->cds_based( ).
I carried out the runtime analysis with the transaction SAT. I focused on the runtime of operations related to internal tables and Open SQL statements. As system I used the “SAP NetWeaver Application Server for ABAP 7.52” (ASE) from the course “openSAP: Writing testable ABAP Code”. The system runs via Amazon Web Services. I repeated the measurement several times. Here is the result of the last measurement.
profile trace results
The first “DB: Fetch ZCALC” relates to the code in method OPEN_SQL_BASED. The second “DB: Fetch ZCALC” and “Loop At IT_13” relate to the code in method ABAP_BASED.
database table times on ZCALC
My question to the community: Have I done the runtime measurement correctly and is this understandable? What do you think? Any comments?
Best regards, thanks for reading and please stay healthy
P.S.: Please support the virtual wishing well.
P.S.S.: Not tired of reading blogs? Check this blog by Johannes Gerbershagen. I really recommend it.
Based on this example - it looks like for performance it doesn't make a difference. Of course I do my performance checks a little different. I think everyone does. Understandable? I can understand it, so it's fine by me.
I would guess - pure guess here - I'm sure someone can help out. The number of fields make a big difference.
As always - great job! I love reading these.
At least no big difference. At the moment it’s not clear to me why ABAP CDS seems to be the slowest solution. Although everything is fast enough. I was really surprised. I guess it’s the database. But that’s no checked fact ?
Addition is a dead simple operation for both ABAP and the database, so your measurements will mostly show the overhead of using a database. The CDS example is going to be slightly slower still because you have an extra column for the result. Although the sum is computed and not stored, the result still has to be transferred
over a network(it’s in memory, but memory is not instant either, that's just marketing).
If you want to see the benefits of pushing down code, your use case should either involve more complex computation or reduce the amount of records transferred.
Instead of doing 2,000,000 sums, try calculating the sum of the sum column. The database will be much faster because it only sends one number back, while in ABAP you would transfer all the numbers just to sum them.
Another thing - the database might decide to parallelize a computation behind the scenes, while parallel processing in abap requires a lot of scenery.
And a CDS is strong at the database layer. So a little join here and there and also an association would make the example visa versa and put the cds on top of the list, no matter if you calculate things at the same time.
Hi Frederik. Thanks for the explanation. Looks like another example 😉
Michael, I was wondering why you didn't include an AMDP access in your solution, and it was only after doing that myself, and looking at the ST12 performance traces, that I realised that the AMDP is basically doing the same as the open SQL example.
I had nice effects from the initial data load of the table too: my ABAP baby code of single insert took 15 minutes to complete vs seconds for insert from table. Who would have guessed? 😀
Keep 'em coming.
Hi Ged, nice idea. I didn't had the AMDP in mind when I was working on my example. So your idea is a great addition. Just a question: Did the AMDP influenced the insert? If possible please provide a blog about that situation. I'm really interested to see what happened.
Sorry Michael, my butterfly mind hopped from one subject to another. For the 'zcalculation_example' I created an AMDP class with a method like this:
BY DATABASE PROCEDURE FOR HDB
summand_1 + summand_2 as summup
ORDER BY example;
It looks just like the open SQL, no?
For the load of ZCALC, I didn't think about using an AMDP, but I'll give it a try right now after your suggestion 🙂