Earlier this year I took part in the SAP PRESS Book Club webinar series, where I answered reader questions about ABAP development over the course of an hour. We had so many questions submitted that I wasn’t able to get to them all!
I wanted to take a little more time and answer three of the questions that we couldn’t talk about live. So read on to learn about CDS entities and views, the ABAP RESTful application programming model, and JOINS.
Q: You mentioned in your book ABAP to the Future that we need to follow this order: 1) coding in ABAP, 2) CDS entities, 3) AMDP. What we are seeing is, in some cases, CDS entities become very slow, maybe due to too many joins (associations and compositions). In fact, one of SAP's function modules (AC_DOCUMENT_RECORD) uses CDS views internally, and we experienced that it is slow in SAP S/4HANA compared to SAP ECC. What is your recommendation in using CDS views/entities? How do we make our Z programs run super fast? It seems that rewriting using joins and new ABAP features takes a good bit of time.
A: There are several points to talk about here. At one stage, SAP recommended starting with ABAP, then CDS, then AMDP, on the grounds that the first two were “open” (i.e. worked on any database). Horst Keller, who writes the official SAP documentation, said that “stay open where possible” was the guideline. In the current releases of ABAP (which admittedly at the moment few outside of SAP have, but everyone will have eventually) this no longer makes sense because Open SQL is now ABAP SQL (which only works on SAP HANA) and CDS views are now CDS entities (which only work on SAP HANA), and AMDP only ever worked on SAP HANA. So, there is no “open” concept anymore. Thus, I would say the new guideline is to use a CDS view/entity if you want integration with other technologies like BOPF/RAP/UI5, or if you would like to take advantage of the fact you can have a 30-character long name to make the calling code read more like English. SAP attempts to keep functional parity between ABAP SQL and CDS, but in reality, there ae some things you just cannot do in one but can do in the other, and that naturally would tip the balance. Of course, there are some things you cannot do in either that you can do in an AMDP. Some examples are having multiple result sets, LOOPS and the like, and concurrent database access—that is, if you have two independent database queries in an AMDP which then get merged before returning the final result, then the two queries will run at the same time rather than one after the other (though naturally both have to finish before the next step occurs).
With a CDS view, it was a total myth that it ran faster than an Open SQL statement. After all, the CDS View worked by generating a DDIC view which did an Open SQL statement in the background, so the database was getting the exact same query and so would take the exact same time to return the result.
With a CDS entity there is no DDIC view—the SQL query is generated at runtime—so in theory it can be a bit more clever (e.g. do not do a JOIN on tables containing data that was not requested at runtime). However, CDS entities are so new I cannot yet say if this actually makes a difference in real life.
What I can say with utter certainty is that standard SAP function modules are always going to be slower than custom function modules (and if it is custom it should be a class) for an obvious reason—standard SAP has to cater for all countries and business types and so must bring back every single field and do a JOIN on every possible table where such data might live in. With a custom equivalent, you only query the data your organization needs, and that is much faster. I have seen lots of people using standard SAP function modules—especially for document flow—to get a very small amount of data and wonder why it takes so long.
To summarize: there is no inherent reason a CDS view/entity with lots of associations should be slower than an ABAP SQL statement with lots of JOINS, if they are accessing the exact same data in the exact same tables.
As to re-writing queries when moving from SAP ECC 6.0 to SAP S/4HANA, yes, you must. They will run without syntax errors in the new environment, but they will be slower.
The book ABAP Development for SAP HANA has an example on optimizing programs after an SAP HANA migration, and we see the following:
- The database migration on its own reduces the runtime of a custom report by over half, but it is still not fast enough to run in dialog.
- The ABAP code needs adjusting, which yields almost as big an improvement: the code inspector (ATC) checks mean you can do this in advance of the migration to SAP S/4HANA.
- Lastly, the database access and associated logic needs to be “pushed down” to the database. Working out when it makes sense to move the processing to the database is not always as clear cut as you might like, but in general you are looking for cases where enormous amounts of data are transferred from the database to the application server (into one or more internal tables), and then a load of calculations and aggregations and so on is performed on that data via ABAP.
- The resulting runtime is 2% of the original runtime. That is fantastic, but the database migration on its own did not get you there—you have to change your Z code in order to truly reap the benefits of the new system.
Q: We are struggling a bit with using inner/outer JOINS in ABAP 7.55. Can you suggest any good reading to get a good grasp of joins? We always ended up coding individual SQL statements in the past (SAP ECC and before ABAP 7.4), and joins are a new area for us.
A: JOINS were introduced to ABAP in version 4.0 of ABAP circa 1997 as I understand it. However, as you correctly noted, many people still struggle with the idea—this is not an SAP-specific thing, all databases have this concept.
The basic idea is that instead of getting a list of sales order headers from VBAK and then looping through those headers and getting the items for each one from VBAP, to get both the header data and the item data in one big hit by doing an INNER JOIN on VBAK and VBAP at once. The performance is thousands of times faster.
Your friend here is transaction ST05 which can analyze an SQL query at runtime and tell you what the database is doing (i.e., if it is behaving in a logical sensible manner, or an insane manner). The latter can happen for all sorts of reasons, usually due to bad index design by the programmer.
You ask for a good reference source, so I am going to blow my own trumpet and suggest a book I wrote on improving the quality of ABAP code which includes a big section on performance.
For the very technically minded there is an SAP PRESS book which goes into great detail about what tricks the SAP HANA database does to optimize performance. This information may help a programmer work with the database, not against it.
Q: We are on an SAP S/4HANA on-premise system. Do we need to worry about the ABAP RESTful application programming model (RAP) and all the stuff in the cloud? We do not have multiple SAP instances with different versions. Can we use ABAP RESTful application programming model on on-premise SAP S/4HANA (we are on 2020). If so, can you let us know how?
A: Check out this SAP Community blog post. However, I would like to add this quote from the film Sleeping With The Enemy starring Julia Roberts:
Hero: Are you all right?
Heroine: I am going to be.
In my humble opinion RAP is like that. It is evolving so fast at the moment that programming in it is futile, as you have to rewrite all your code every three months as new versions come out which give the existing code syntax errors. SAP insists it is production-ready right now, and indeed companies like LEGO use it productively, but I would say give it another two years then it will be stable.