It is a very common question nowadays, which one to chose when hitting the database? Option candidates are New Open SQL, CDS(Core Data Services), AMDP(ABAP Managed Database Procedure) or CTE(Common Table Expression). They all are designed for new enhanced ABAP and they all are made for delivering performance. Moreover, all of them do code push down!
Disclaimer: Use this article and its derived conclusion with your own conscience. The author doesn’t take any responsibility if you get other results and lead to another conclusion.
For comparison, we take a scenario of joining four tables. We join tables using new Open SQL, CDS, CTE and AMDP by keeping the same query across different approaches.
You can find the code on GitHub and clone for yourself to test it.
You may also like to check Best Practices in Code Push Down with S4 HANA.
To compare runtime for each, I am executing each for 10 times and taking an average of them.To compare runtime for each, I am executing each for 10 times and taking the average of them.I am not giving time for CTE because it has a different use case than a simple join. You can anyway go ahead and execute by yourself to see CTE performance.
All Times in milliseconds!
Open SQL CDS AMDP
- 264.16 228.28 197.63
- 178.75 204.22 223.59
- 210.28 201.12 219.49
- 203.02 184.15 223.47
- 207.51 170.6 221.25
- 182.65 207.63 207.4
- 194.28 170.00 209.98
- 195.05 181.01 238.32
- 215.18 170.15 232.45
- 184.97 161.35 208.81
Avg: 203.58 187.85 218.23
Conclusion: Open SQL, CDS, and AMDP are not competitors for each other and they have different use-cases.
In this particular case of joining 4 tables, CDS is clear winner followed by open SQL and AMDP.