Code Pushdown

As we know when we move to a HANA database the idea is to go pull on for “code pushdown”.

The bottleneck for performance has traditionally been moving records between the database and the application server.

The more records that get moved, the longer it takes. Once the data has made it to the application server you do all sorts of calculations upon it using ABAP.

The “new” idea is to do all the calculations at the database level, and then only transfer a small amount of records (the result) to the application server. The HANA database is optimised for this, it does all sorts of tricks e.g. paralleled database reads. The end result – in theory – is that if you “push down” data intensive calculations to the database then your application will run one billion times faster.

At this point you are probably saying “oh yes, everyone says this code pushdown thing is so great, no-one ever gives a practical example”.

Well here I go with one. It is not a very good example, but it is realistic – which beats the pants off many other examples I have seen. If you have a better example – and I am sure you do  then tell me what it is. However what I am about to describe is something I have dealt with every month for twenty years.

You probably know the VBKD “business data for sales orders” table which contains things like customer purchase order numbers and payment terms, and it works like this – if every single line item in a sales order is identical then there is only one entry for the sales order with an item number (POSNR) of 000000. If the person entering the sales order changes such a value on line item level then a new entry is added to the table with the POSNR of the item being changed. This design was clearly intended to save database space – what is the point of having ten identical entries for ten lines items plus another identical entry for the header?

All well and good, but this means that programs generally read in the entire contents of VBKD into an internal table. Then traditionally you read that internal table with the item number and if that does not work then you do another read on the header level. That used to require two READ TABLE statements in ABAP but in recent releases you can do this in one statement as shown below

DATA(ls_vbkd) = VALUE #( lt_vbkd[ vbeln = ls_vbap-vbeln
posnr = ls_vbap-posnr ]
DEFAULT VALUE  #( lt_vbkd[ vbeln = ls_vbap-vbeln
posnr = '000000'] ) ).

That is great – however you would think if we can now do clever things like that on internal tables, surely we can do clever things like that whilst reading the database in the first place? That is, we just want one record transferred to the application server from the database – the item record (if it exists) or the header record – that is we wish to “push down” the logic shown above to the database layer.

As it turns out I could not find a way to do that in an ABAP SQL statement (which does not mean there is not a way to do it, you can tell me how if you want) nor in a CDS view.

In an ADMP however you can in theory create a class that takes in a sales order number and item (or more likely a table of such) and returns the correct VBKD record (or more likely a table of such).

That would be written in SQLSCRIPT syntax which might make your head spin e.g. the idea of FOR / ENDFOR instead of a LOOP / ENDLOOP sends some ABAP people running for the door. That shouldn’t be the case, all programming languages have different syntax, and it’s nothing to be scared of.

Regardless the concept is clear – inside the database the ADMP tries to get the record for the order / item combination and if that does not exist gets the record for the order/header combination.

OK then let us try to create an ADMP to do just this.

I go to my lovely ABAP in the Cloud system which has a HANA database attached. There is no VBKD table there, so I must create my own Z equivalent for this exercise.

What do we often look for in VBKD – the payment terms, which say something like “30 days after month of invoice”. Now when you are buying Monsters from the Baron the variable is not so much how long you have to pay, rather what will happen to you if you don’t pay.

So, I set up a domain which lists the various payment options.

Domain

The last option is by far the worst. People on those payment terms tend to pay in advance.

Next I create a data element where that domain will live. Now I will create the equivalent of VBKD. To keep things as simple as possible this is only going to have tree fields – the Monster sales order number and item and the payment terms. I have to create the VBELN and POSNR data element equivalents as well, as there is no such thing as VBAK and VBAP in ABAP in the Cloud. I will call my new table ZMONSTER_BDATA.

Table

DDIC definitions in ABAP in the Cloud are all source code based, which takes some getting used to, but after all it is not that different to defining internal tables.

Next I need a little program to fill that table with bogus data. In the old days I would write a type 1 executable program in SE38. However, that is so ten minutes ago. In “Steampunk” I need to create a class. If that class implements interface IF_OO_ADT_CLASSRUN then I can invoke the MAIN method directly, just like pressing F8 on the SE38 screen.

Generator

I don’t want anything fancy – in normal ABAP world if I wanted to get the pertinent records of item 10 of Order number 1 and Item 10 of order number 2 then I would have to transfer three records from the database to the application server and then filter out the header level record for order number 2.

Instead I just want two records brought back – the correct record for each order. With just two orders this makes no difference but imagine if you had ten thousand or more, which is often what I get in real life.

To fill up the table I select my class in the explorer view on the left-hand side of the Eclipse screen and take the menu option “Run As => ABAP Application (Console)”. Hooray! It tells me the three records have been saved to my (HANA) database!

Next step is to write a new class which will query the database for my two monster sales orders and bring back one and only one record from the database for each of them.

I need to flag the reading class with the marker interface IF_AMDP_MARKER_HDB so the implementation can be executed as a stored procedure inside the HANA database.  In theory (according to the documentation) I should be able to have a RETURNING parameter but the first error came when I tried to get the whole structure back as a RETURNING parameter and the second error came when I tried to get ANYTHING back as a RETURNING parameter so I have fallen back on an EXPORTING parameter.

The definition therefore looks like this: –

Definition

And the draft implementation like this: –

Implementation V1

That is the easy bit, everything in traditional ABAP (more or less) up until now.

The next step is for me to jump right out of my comfort zone and start coding in SQLSCRIPT. That is very scary. So, I do a Google search and it is no help whatsoever. Even the SAP help is no use. So, it is guessing time. That is good, it is the best way to learn. It is like the Agile Manifesto. https://agilemanifesto.org/

“Experimenting Yourself” over “Being told how to do something”

While we believe there is value on the statement on the right we believe here is more value in the statement on the left.

Going back to messing around in SQLSCRIPT Eclipse gives me error messages guiding me how to write in this new language – first up you end lines with a “;” not a period. After a while of looking on SCN to try and work out the syntax and trying assorted things at random I come up with the following: –

Implementation V2

That appears to work just fine. You could do that in ABAP – two SQL statements one after the other, or read the whole table and then do two reads on the internal table to try and get the correct record.

Here all the “smarts” if you can call it that, are on the database level. The obvious question is – what is the difference? Presumably that makes things faster? Does it? Tell me if it does not.

This is the first SQLSCRIPT I have ever written. I am sure I have got it horribly wrong. The very first ABAP I wrote in 1999 was not very good either, the very first BASIC I wrote in 1981 was not up to much.

So, if someone wants to tell me what I SHOULD have done then I am all ears.

Cheersy Cheers

Paul