Code pushdown part I – or “the re-discovery of the power of joins”
With HANA all around (speaking about the HANA database, not HANA XS or Hana Cloud) I as ABAP developer expect myself needing to leverage more database features. This consequently means more to code more SQL – and this implies more headache to me. My brain more used to more imperative commands instead of declarative expressions.
But technology does not care about my brain, so I have to get it used to this type of programming. Luckily, I don’t need a HANA-DB in order to train myself, so I started to write a short series about how to use SQL – oh wait, it’s now called “code pushdown”. This makes it easier to sell and might trick my brain that this is not veeeery old stuff which I should have been knowing for a long time but funky new stuff which is worth investing time into.
This first post is about the very basic of joins – particularly LEFT OUTER JOINs in combination with restrictions.
In our application, the database scheme is quite well normalized (don’t ask me which normal form it is, but it should be the third one or higher, I was never eager to differentiate further). However; to the user, the data is presented in a denormalized form. While transactionally, the dependent data is retrieved via an association, we also need to enable finding instances where dependent data matches some criteria. However, the non-existence of this dependent data must not prevent finding instances when not searching for attributes of the dependent data.
Reading the requirement, the first thing that comes to every ABAPer’s mind is a database view. Go to se11, model your relations, use OpenSQL – that’s it. Unluckily not.
An DDIC DB-view always joins tables with an INNER JOIN. This means that the right hand side table (RHST) needs to provide matching entries for each instance of the Left HSTas prerequisite for the joined data to exist. This makes DDIC DB views unusable for all those selections, where data shall be searchable which has a minimum cardinality of 0. Thus, specifying the join as LEFT OUTER at runtime seems to be the only alternative. However, as we are being told by the OPenSQL-exception, placing a restriction on the RHST via OpenSQL is also not possible. Both limitations – as far as I know – shall be obsolete when using Core data services (CDS). But as this is only available from 7.40 upwards, it can only be subject of another post.
Until then, in our project we were forced to used native SQL based on LEFT OUTER JOINs in order to fulfill those requirements.
The INITIAL issue
After we had implemented a generic query class executing the native SQL, we felt quite comfortable having found a nice solution. Until the first tickets appeared in out bug tracking tool. As we’re using a floorplan manager based UI, our users were enjoying the “new” search options – particularly the “is initial” and the “is not initial” option were attracting a lot of affection: “oh, this makes it so easy for me to find a … where no purchasing requisition has been created yet”. Of course! “But it does not work!”. The reason for that was the custom table containing the purchasing requisition number was a depend one to the table which contained the instances for which shall be searched. With dependent_table.purch_req = ‘’, the desired instances for which the dependent data does not exist were not returned. When explaining the issue and solution in my team, I felt that not every ABAPer is aware of that behavior.
Thus, I wrote a short report executing multiple selects in order to explain the different result sets.
Have look at the following screenshot picturing the content of the joined tables and the different SELECT-statements issued. Do you know all the results?
You can find a screenshot of the report including result and explanations separately at below. I did not want to spoil your curiosity presenting the results directly 😉
Did you expect all the results? There was one, which surprised me. And to my even bigger surprise, it behaves differently on NW 7.31 and NW 7.40 – maybe I should open a message for that one…
You can find the report attached including the report source and alternatively SAPLINK-nugget containing the source tables if you’re too lazy to create the two tables yourself.
There is one massive limitation with respect to performance on this approach of using LEFT OUTER JOINs. Therefore, in a subsequent post, I may introduce the advantage of subqueries (once I understood it myself to such an extent that I feel comfortable writing about it… )
I’d love to read your comments on this topic and how you prepare yourself for the obviously upcoming re-incarnation of SQL – sorry: of the code pushdown.
Dear Oliver Jaegle,
Thanks for this nice blog.. Very informative. I will surely comeback and share my thoughts here....But just would like to mention that Code Push Down using Open SQL is not limited to the use of JOINs. Nevertheless in many cases in the existing code where lot of things that could have been achieved using efficient JOINs might have been done in the ABAP layer after fetching huge volumes of data(which could have been a valid approach at the time the code was written and the underlying DB).These cases in the context of HANA should surely result in performance optimizations using JOINs.
But going beyond the the Open SQL syntax has been enhanced starting NW 7.4 SP5 in order make Open SQL support features more closer to the features of Standard SQL(SQL92 Specification) starting release AS ABAP 7.4 SP5
You can find more information on this here.
These features have been further enhanced in AS ABAP 7.4 SP8 which is summarized in the in the blog ABAP News for 7.40, SP08 - Open SQL by Horst Keller
Also I just wrote a short blog trying give some information on why Code Push Down for HANA already can start with Open SQL here
Code Push Down for HANA Starts with ABAP Open SQL
I will look forward to your feedback. Thanks once again for this very informative blog and for sharing good pratical examples. I will come back here to comment after looking at the examples.
of course you're right: Using JOINs is only one expression in order to push code down. I wanted to start a series with different aspects of SQL: Joins, aggregations, subqueries, In-built functions. But the title did not express that, I adopted it.
I know about the enhanced features of OpenSQL, thanks for linking it though. Also, CDS-views solve many of the limitations (e. g. joining left-outer on a left-outer joined table). But still, this is a glimpse into the future (most probably not only for myself, but also for others). And at the same time, (Native-) SQL has been offering many features for decades now. I wanted to point this out as well and poke some fun at the expression "code pushdown".
Thanks for your feedback!