In his recent blog post " Code Pushdown"Paul Hardy, a long-time SAP community contributor described his first foray into HANA SQLScript programming.
Paul picked a very ABAP-y problem to solve:
Finding records with fallback to a default value.
I won’t go into too much detail where and how that is done in SAP ABAP-based solutions, just this:
For a group of records (like an order made up of order items), there are matching records in another table.
The rule to get those matching records is to check whether there is a full match for an item in the table.
If there is, this match should be used.
If there isn’t a match, another, a default entry (that is guaranteed to be there) should be used.
Paul’s example is about orders with order-items that can have different payment terms. Each item can have its own payment terms but will use the default payment terms that are defined by the “header” order-item (which is item '00000000' in the order items table).
It’s not hard to think of other examples for something like that.
Machine configurations could be such a thing.
Or variations of pizza toppings.
Olden days and olden ways #
Paul explains that the typical ABAP approach was to simply try and read the specific payment terms for each order item and do a second read to get the default value if the first read did not get anything back.
That’s two ABAP <=> DB roundtrips for just one record.
Worse, the application does not add any logic here; the database (not just HANA) can very well determine whether a SELECT returns something or not on its own. No confirmation or double-check from the application required.
Newer ABAP language versions provide some syntactic sugar for this scenario:
they allow for specifying how a DEFAULT record should be computed.
That’s an improvement but only for making the ABAP code better express the intention of the developer.
Paul is solving the problem #
Like probably many ABAPers would do, Paul created an AMDP (ABAP Managed Database Procedure) that mimics the ABAP logic 1:1.
As admitted in the post, this was his first SQLScript ever written.
Considering this, it’s a success as it delivers the correct results… sometimes.
The code reads the data for an order item into an SQLScript table variable, like so:
bdata = select ... from <table> where <conditions>;
Then, assuming there will be at most one record in the result set, a column (part of a table) is “extracted” into a variable (a scalar value).
ed_payment_terms = :bdata.paymentterms[1];
IF :ed_payment_terms <> '' THEN
RETURN;
END IF;
The problem here is that if there is no record in bdata, accessing the table variable on index 1 will fail.
The style faux-pas to use = instead of := can certainly be attributed to the lack of good programming guides/manuals for SQLScript developers.
I agree with Paul’s sentiment that the SAP documentation is not really helpful here (and it’s not really supposed to: SAP’s documentation is a legal document outlining the features and expected behaviours of the systems they sell).
The more SQL-y way of doing all this would be to use
select
…
INTO ed_payment_terms
from <table>
where <conditions>;
This would also fail with a NO DATA FOUND error, if there was no matching record.
One way to deal with this is to implement some error handling into the procedure, which is verbose and tedious (but required in pre-HANA 2.3).
With recent HANA versions one can use the DEFAULT VALUE clause to safeguard against this error (see what SAP did here?)
select
…
INTO ed_payment_terms DEFAULT 'some default value'
from <table>
where <conditions>;
Don’t stop here though #
The problem with this very common approach of duplicating ABAP/application logic in SQLscript is that it often only reduces data transfers between application and database.
It does not take advantage of what the SQL environment could do here to further increase performance.
One early commenter to the post provided a solution in ABAP CDS that performs multiple outer joins between the items table and the payment terms table (the commenter uses the actual ABAP tables, but that doesn’t matter here) to
first figure out if an item has a “special” payment term or uses the default and
then provide the correct join key to a second join that actually computes the result set.
This, also, solves the problem and provides an easy-to-use database view that shows all correct match-ups for all items. This can be easily consumed in ABAP, does not require creating a stored procedure (which always bears the overhead of invoking the SQLscript engine in HANA, which means there is a performance/resource penalty compared to “straight” SQL) and fits easily into the modern ABAP/CDS development flow.
So, that’s fine, isn’t it? #
For the scenario Paul described, the CDS view approach is probably fine, can be thought up and implemented in an hour or so and is an improvement over the old-style ABAP approach.
There are of course alternative options and I looked into some of them, mostly a modelling exercise for myself.
I thought of
- sqlscript select into with error handling i n case no data is found
- only suitable for single records lookup
- added SQLscript overhead
- sqlscript select into with DEFAULT clause
- only suitable for single record lookups
- added SQLscript overhead
- SELECT with LEFT OUTER JOIN and check for NULLs
- potentially big overhead
- UNION with best record select (i.e. MAX + JOIN or RANK/ROW_NUMBER() + FILTER)
- potentially big overhead
- also, UNION is not necessary as all DEFAULT/special entries are in a single table anyhow
- keep track of non-default entries in table b, i.e. have a flag there to indicate that there is a non-default setting
- would require additional updates, maybe via triggers (ref. constraints won’t do that. Alternatively the application code could do that)
- would address the technical issue of not knowing if there should be a match before doing the join/select on table b
- would keep storage requirements in table a and the number of records in table b low
- “unfold” the default cases to ensure all records in table a have a distinct matching record in table b
- leads to potentially many more records in table b (especially if records in table a most often use the default)
- but column store compression could reduce this again
Thinking good, testing better #
Then I went and did a test implementation for the ones that I found promising.
This step - implementing a version and testing it out - is something I can only recommend.
It is so easy to fall in love with a clever solution in your head.
If it does not survive a test, it shouldn’t get into production.
Test setup #
Like Paul, I want some data to play with.
I went, however, for some more data than he did (MONSTER table naming inspired by Paul’s post):
create column table MONSTER_BUSINESS_ITEMS
( client NVARCHAR( 3) NOT NULL
, order_number NVARCHAR(10) NOT NULL
, item_number NVARCHAR( 8) NOT NULL
, item_info NVARCHAR(80) NOT NULL DEFAULT ''
, primary key (client, order_number, item_number));
create column table MONSTER_BUSINESS_ITEM_DATA
( client NVARCHAR( 3) NOT NULL
, order_number NVARCHAR(10) NOT NULL
, item_number NVARCHAR( 8) NOT NULL
, payment_terms NVARCHAR(10) NOT NULL
, primary key (client, order_number, item_number));
--- generate some test data
--- 10000 orders with 20 items each
insert into MONSTER_BUSINESS_ITEMS (
select
'007' as client
, ABAP_NUMC(orders.generated_period_start, 10) as order_number
, ABAP_NUMC(items.generated_period_start, 8) as item_number
, 'info for item '
|| orders.generated_period_start
||'/'|| items.generated_period_start as item_info
from
series_generate_integer (1, 1, 10001) orders
cross join
series_generate_integer (1, 0, 20) items
);
-- create DEFAULT entries for all orders (=> item_number = '00000000'
-- as different orders may have different DEFAULTs, just add variety
insert into MONSTER_BUSINESS_ITEM_DATA (
select
client
, order_number
, item_number
, map(mod(order_number, 3)
, 0, 'DEFAULT0'
, 1, 'DEFAULT1'
, 2, 'DEFAULT2'
, 'DEFAULTX') as payment_terms
from
monster_business_items
where
item_number = ABAP_NUMC(0, 8));
select count(*) from monster_business_items; -- 200.000
-- create NON-DEFAULT entries for 5% of all items with item_number != '00000000'
-- remember to insert NON-DEFAULT values!
insert into MONSTER_BUSINESS_ITEM_DATA (
select
client
, order_number
, item_number
, 'SPECIAL'
from
monster_business_items TABLESAMPLE BERNOULLI ( 5 )
where
item_number != ABAP_NUMC(0, 8)
order by
client, order_number, item_number
);
-- - Rows Affected: 9443
Note: I deliberately made use of the SERIES_GENERATE_INTEGER, ABAP_NUMC and TABLESAMPLE functions, as these belong to the functions that have been available in HANA for some time but that barely gets used. Even worse, developers build their own versions of such functions, because they are not aware of the existing ones.
Ready, steady, go #
Now, we have 200.000 order_items and about 5% of them have non-default payment terms.
The data looks like this:
CLIENT ORDER_NUMBER ITEM_NUMBER ITEM_INFO
007 0000000001 00000019 info for item 1/19
007 0000000001 00000018 info for item 1/18
007 0000000001 00000017 info for item 1/17
007 0000000001 00000016 info for item 1/16
007 0000000001 00000015 info for item 1/15
007 0000000001 00000014 info for item 1/14
007 0000000001 00000012 info for item 1/12
007 0000000001 00000011 info for item 1/11
007 0000000001 00000010 info for item 1/10
007 0000000001 00000009 info for item 1/9
007 0000000001 00000007 info for item 1/7
007 0000000001 00000006 info for item 1/6
007 0000000001 00000005 info for item 1/5
007 0000000001 00000004 info for item 1/4
007 0000000001 00000003 info for item 1/3
007 0000000001 00000002 info for item 1/2
007 0000000001 00000001 info for item 1/1
007 0000000001 00000000 info for item 1/0
007 0000000001 00000008 info for item 1/8
007 0000000001 00000013 info for item 1/13
[...]
CLIENT ORDER_NUMBER ITEM_NUMBER PAYMENT_TERMS
007 0000000001 00000000 DEFAULT1
007 0000000001 00000013 SPECIAL
007 0000000001 00000008 SPECIAL
007 0000000002 00000000 DEFAULT2
007 0000000002 00000017 SPECIAL
007 0000000003 00000000 DEFAULT0
007 0000000004 00000000 DEFAULT1
007 0000000005 00000000 DEFAULT2
007 0000000006 00000000 DEFAULT0
007 0000000007 00000000 DEFAULT1
[...]
E.g. for ORDER_NUMBER 1 all but ITEM_NUMBER 8 and 13 have the DEFAULT1 payment terms.
OPTION: BEST MATCH #
-- best record select (i.e. MAX + JOIN or RANK/ROW_NUMBER() + FILTER)
-- requires that there is an entry for the item_number to work
--- this utilizes the ordering of item_numbers and ‘000000000’ indicating the default item
--- basically, it sorts all item_numbers of a specific order number and picks the “first” one
--- which means the “smallest” value as we order in ascending order and put NULLS last
select
bi.client, bi.order_number, bi.item_number
, bid.payment_terms as pay_terms_by_simple_join
, MAP (bid.payment_terms
, NULL
, FIRST_VALUE(bid.payment_terms)
OVER (PARTITION BY
bi.client, bi.order_number
ORDER BY
bi.client, bi.order_number, bi.item_number ASC
NULLS LAST)
, bid.payment_terms ) as pay_terms_with_default_fallback
from
monster_business_items bi
left outer join monster_business_item_data bid
on (bi.client, bi.order_number, bi.item_number) =
(bid.client, bid.order_number, bid.item_number)
order by
bi.client, bi.order_number, bi.item_number;
CLIENT ORDER_NUMBER ITEM_NUMBER PAY_TERMS_BY_SIMPLE_JOIN PAY_TERMS_WITH_DEFAULT_FALLBACK
007 0000000001 00000000 DEFAULT1 DEFAULT1
007 0000000001 00000001 ? DEFAULT1
007 0000000001 00000002 ? DEFAULT1
007 0000000001 00000003 ? DEFAULT1
007 0000000001 00000004 ? DEFAULT1
007 0000000001 00000005 ? DEFAULT1
007 0000000001 00000006 ? DEFAULT1
007 0000000001 00000007 ? DEFAULT1
007 0000000001 00000008 SPECIAL SPECIAL
007 0000000001 00000009 ? DEFAULT1
007 0000000001 00000010 ? DEFAULT1
007 0000000001 00000011 ? DEFAULT1
007 0000000001 00000012 ? DEFAULT1
007 0000000001 00000013 SPECIAL SPECIAL
007 0000000001 00000014 ? DEFAULT1
007 0000000001 00000015 ? DEFAULT1
007 0000000001 00000016 ? DEFAULT1
007 0000000001 00000017 ? DEFAULT1
007 0000000001 00000018 ? DEFAULT1
007 0000000001 00000019 ? DEFAULT1
[...]
This approach performs only a single LEFT OUTER JOIN and then uses MAP (same thing as CASE) to handle the empty-matches.
For those records, where there was no specific entry in the ITEM_DATA table, we want to use whatever value is used for the “header-item” with ITEM_NUMBER 0.
This is here done with the WINDOW clause and the FIRST_VALUE analytic function.
This function creates a “window” for the current row that includes all rows with the same CLIENT and ORDER_NUMBER ( PARTITION BY).
Then this data is sorted by CLIENT, ORDER_NUMBER and ITEM_NUMBER in ascending order with all NULL values at the end of the sorted data ( ORDER BY … NULLS LAST).
Finally, the FIRST_VALUE function picks, well, the FIRST_VALUE which will be the default payment term for this ORDER_NUMBER.
When packaged up into a view this can be used like a normal table:
create view monster_business_items_pay_terms_WINDOW as
(select
bi.client, bi.order_number, bi.item_number
, bid.payment_terms as pay_terms_by_simple_join
, MAP (bid.payment_terms
, NULL
, FIRST_VALUE(bid.payment_terms)
OVER (PARTITION BY
bi.client, bi.order_number
ORDER BY
bi.client, bi.order_number, bi.item_number ASC
NULLS LAST)
, bid.payment_terms ) as pay_terms_with_default_fallback
from
monster_business_items bi
left outer join monster_business_item_data bid
on (bi.client, bi.order_number, bi.item_number) =
(bid.client, bid.order_number, bid.item_number)
);
select
CLIENT, ORDER_NUMBER, ITEM_NUMBER
, PAY_TERMS_BY_SIMPLE_JOIN
, PAY_TERMS_WITH_DEFAULT_FALLBACK
from
monster_business_items_pay_terms_WINDOW
where
client = '007'
and order_number = ABAP_NUMC(1, 10)
and item_number = ABAP_NUMC(3, 8); -- (ABAP_NUMC (13, 8))
CLIENT ORDER_NUMBER ITEM_NUMBER PAY_TERMS_BY_SIMPLE_JOIN PAY_TERMS_WITH_DEFAULT_FALLBACK
007 0000000100 00000003 ? DEFAULT1
CLIENT ORDER_NUMBER ITEM_NUMBER PAY_TERMS_BY_SIMPLE_JOIN PAY_TERMS_WITH_DEFAULT_FALLBACK
007 0000000001 00000013 SPECIAL SPECIAL
Attacking the core problem - Loss of knowledge #
Taking a step back from fancy SQL implementation tricks, the core of this problem is in fact not the “double-dipping” of the application code.
That really is only the symptom of the actual issue.
What we have here is a situation of knowledge loss:
When the user of the application decided that ITEM_NUMBER 13 should have its own payment terms, the system and the user “knew” explicitly that this was the case.
The user would not have to look up again if there is a special payment term for item 13 to recall this fact.
Yet, the application (and data-) designers decided to throw away this information and rely on looking it up.
What if - for some reason - that entry for ITEM_NUMBER 13 got removed from the MONSTER_BUSINESS_ITEM_DATA table?
Nobody would ever know that there was supposed to be an entry.
And all our implementation efforts up to here basically tried to work out this little piece of information (whether the default or the special terms apply) and then fetch the data accordingly.
What if we never lost that information?
Could we do better?
Of course, we can!
All it takes is a sort of indicator or flag for every ITEM_NUMBER that represents this bit of information.
This indicator could easily be maintained by the application that works on this data anyhow; when inserting data into the ITEM_DATA table it could just update the flag.
Alternatively, one could retrofit database triggers (no, no, no!) that would change the flag whenever there are inserts/deletes on the ITEM_DATA table.
Let’s assume the maintenance has been taken care of and we can use the flag. What would that look like?
Add the flag - “default” for using the default conditions #
alter table monster_business_items
add (DEFAULT_CONDITIONS_APPLY NVARCHAR(1) NOT NULL DEFAULT 'X');
Delete flag for those with special conditions #
update monster_business_items bi
set
bi.default_conditions_apply = ''
where
exists (select *
from
monster_business_item_data bid
where
(bi.client , bi.order_number , bi.item_number)
= (bid.client, bid.order_number, bid.item_number));
Finally, joining data with confidence #
-- now we know by the flag if we need to join on '00000000' or the item_number
select
bi.client, bi.order_number, bi.item_number
, bi.default_conditions_apply
, bid.payment_terms as pay_terms_by_simple_join
from
monster_business_items bi
inner join monster_business_item_data bid
on (bi.client, bi.order_number)
= (bid.client, bid.order_number)
and bid.item_number = MAP (bi.default_conditions_apply
, 'X', ABAP_NUMC(0, 8)
, '', bi.item_number)
order by
bi.client, bi.order_number, bi.item_number;
Again, I put this into a view:
create view monster_business_items_pay_terms_FLAG as
(select
bi.client, bi.order_number, bi.item_number
, bi.default_conditions_apply
, bid.payment_terms as pay_terms_by_simple_join
from
monster_business_items bi
inner join monster_business_item_data bid
on (bi.client, bi.order_number)
= (bid.client, bid.order_number)
and bid.item_number = MAP (bi.default_conditions_apply
, 'X', ABAP_NUMC(0, 8)
, '', bi.item_number)
);
and check the result:
CLIENT ORDER_NUMBER ITEM_NUMBER PAY_TERMS_BY_SIMPLE_JOIN DEFAULT_CONDITIONS_APPLY
007 0000000001 00000003 DEFAULT1 X
CLIENT ORDER_NUMBER ITEM_NUMBER PAY_TERMS_BY_SIMPLE_JOIN DEFAULT_CONDITIONS_APPLY
007 0000000001 00000013 SPECIAL
Comparing the approaches #
With PlanViz this “FLAG” approach used ~1.8 ms/1.3 MB.
The former approach with FIRST_VALUE clocked in at ~3.0 ms/1.5 MB.
(all values are reduced to a simple average - do not mistake that for proper performance testing!)
That is quite a difference for a statement that just returns a single record.
I have not measured the approaches presented in the blog post but I dare say they would probably be less efficient.
Closing #
Now, I hear you asking, what does DEFAULT_CONDITIONS_APPLY column cost us?
HANA memory is expensive and we cannot waste it…
Since the column only has two distinct values (‘X’ and ‘’) it is highly compressible.
For the 200.000 entries in my MONSTER_BUSINESS_ITEMS table (and 5% special conditions used), this column takes a whopping 55KB.
That, of course, is outrageous, if you plan to manage the data on your C64 but you would already be OK if your parents get you that awesome AMIGA 500+ :)
There you go, now you know.