Horrible Performance, HANA push down to the rescue
ABAP on HANA is a hot topic in forums and training sessions these days. We have been seeing lot of general queries raised by (conventional) ABAPers about converting ABAP based business logics to ABAP on HANA using CDS and AMDP. ABAP , being a simple programming language , developers are not familiarize with core SQL functionalities of a database. Well, then its the time to learn them and design perfect programs for your customer.
We all already know , what is CDS and AMDP and why them. Our topic of interests is , how to map the existing abap logic to SQL blocks / CDS statements effectively. Officially , this is a “Tips and Tricks for beginners”
Recently we had to touch up an existing ABAP report ( for a multi million dollar business ) which was taking nearly 1 hour to execute ( in background ) . We first , applied basic performance improvements to it from ABAP level and brought down the execution time to 20-25 Minutes . Then , pushing down core business logics to database ( using CDS & Table functions ) the execution time came down to less than a second.
Disclaimer : Logics explained below may not be the best performing solution. But , some idea on how all we can look into a case and implement them in ABAP on HANA.
1. View with list of hard coded values
Requirement : Generate a view which returns fixed values based on a condition. For example, I need a CDS view which returns data as below.
There is no DDIC tables involved in this scenario to query and get the result.
Lets create a table function and associate it with an AMDP method. refer this link
and Table function implemented as
Have you noticed the usage of table dummy in the where clause and not declared using the “using” clause in method signature ? dummy is not a DDIC object and not a normal database table. DUMMY is a system table , part of the language construct and has always exactly one row. We can use dummy to perform this type of functionalities. The usage of built-in functions above is self explanatory .
Alternately using local table variable:
2 . Aggregating columns conditionally
In the below example, the requirement is to Plant wise , Material wise stock data for movement types 601 and 602. Condition is , 601 movement type quantity should be subtracted from 602 quantity while aggregating.
Corresponding ABAP logic could be, to send 2 separate SELECT requests for 601 movement type and 602 movement type aggregated based on the Plant , Material and Quantity and then loop over first result set and read second . Then perform the arithmetic operation to arrive at the expected output. Or may be, using new OPEN SQL syntax, add a calculated column based on the movement type and later collect the records in application layer.
Look at the below statement.
Based on the Movement type ( BWART ) , the Menge ( quantity ) field is negated and summated group by plant , material and Unit of measurement .
If you really love ABAP, don’t let it suffer. Let the DB suffer !
3. Logic Block – Fetch entries having 2 statuses
In Plant maintenance , the PM Order view VIAUFKS and Object status table JEST have huge number of records. In the below example, requirement is to fetch orders which has 2 statuses in the order table.
Say, wee need to fetch all orders created this month having 2 status entries in JEST table ‘I0001’ and ‘I0002’ ;
Sample Data and expected output as below.
Here, except order numbers 1000003 & 1000007 , all orders has 2 statuses correspondingly in JEST.
How would you do it in conventional ABAP ?
"Select all the order numbers and Object numbers SELECT ORDERNUMBER OBJECTNO from VIAUFKAS INTO TABLE T_VIA where CRDATE IN IR_IN_DATE. SELECT OBJECTNO , STATUS from JEST INTO_TABLE T_JEST_01 FOR ALL ENTRIES IN T_VIA where OBJECTNO = T_VIA-OBJECTNO and STATUS EQ 'I0001'. SELECT OBJECTNO , STATUS from JEST INTO_TABLE T_JEST_02 FOR ALL ENTRIES IN T_VIA where OBJECTNO = T_VIA-OBJECTNO and STATUS EQ 'I0002'. "Now loop T_VIA , READ I_JECT_01 , If success, READ I_JEST_02, and If success - Note the Object Number.
Both VIAUFKAS and JEST have millions of records and the above code block is costly in terms of performance.
Note : Above code is the worst way to implement the logic. in normal ABAP itself we can optimize the process. For the sake of explaining, I wrote it like this.
Lets see , how can we push it down to a better code.
lt_via = SELECT aufnr , gewrk , auart , objnr , msgrp , beber , gltrs , qmnum FROM viaufks WHERE ( gltrs >= :i_date_from and gltrs <= :i_date_to ) and mandt = :i_mandt; lt_via_out = SELECT * from ( select v3.objnr as objnr , count(*) as cnt FROM :lt_via v3 inner join jest j on v3.objnr = j.objnr AND J.stat IN ('I0001' , 'I0002' ) group by v3.objnr ) where cnt = 2;
Query 1 :
it the variable lt_via, we are selecting all the order numbers , object numbers and other required fields based on the input dates.
Query 2 :
in the subquery ( inside the braces in second sql ) , selecting all the object numbers corresponding to the entries in lt_via and status I0001 and I0002 and count of entries .
Now, the outer select fetches only records having CNT = 2. So , we got the records as required .
4. Logic Block – Entries found in one table and not in other
Title is little confusing; I couldn’t find any better title for this scenario.
Wee have two tables TABLEA and TABLEB. I want to fetch entries from TABLEA , which are not available in TABLEB.
In the below example, the TRIPMASTER table has all the trips created in the system. When a trip is deleted , DELETEDTRIPS table is updated. Requirement here, is to get the trip details which are not deleted. Technically, select entries from “TRIPMASTER” whos trip numbers are not present in DELETEDTRIPS.
An inner join would have sufficed if the requirement was to fetch records found in both the tables. We have many methods to run this logic in ABAP layer; like fetch records, loop the first table, then read from second table and so on.
Let’s try to achieve the same using SQL SCRIPT.
Lets use a left outer join.
When there is no match in the right table for the given join condition, the projection from right table will be null. Which means; If I Left outer join TRIPMASTER and DELETED TRIPS, All the trips which are not found in the right table(DELETEDTRIPS) shall be null. using an wrapper SQL, we can filter the records where field is null.
SELECT TRIPNO , FROM , TO from ( SELECT TM.TRIPNO, TM.FROm, TM.TO , DT.CRDATE from TRIPMASTER TM left outer join DELETEDTRIPS DT on TM.TRIPNO = DT.TRIPNO where ... ) where CREDATE is null;
CRDATE from DETEDTRIPS table will be projected as null, if there is no matching in
What’s in part 2
- How to push selection screen data effectively to HANA layer / CDS
- Analyze pain ABAP logic blocks
- When to choose CDS , Table Function & AMDP logically
- Design thinking -ABAP HANA for Web applications.
- And much more,.
Read my other related blogs.
Title credit : Michelle Crapo
You see this message because you read the whole blog. care to leave a comment and like the blog?
Sreehari V Pillai
Quite confused by this blog... The title is a bit misleading. ABAP is still ABAP and the term "mapping" is usually used when it comes to data. (Translation issue?)
It says "for beginners" but then "We all already know, what is CDS and AMDP". Well, if we're beginners then maybe we don't? Why not just start with a short explanation?
Item 1: why is this a table function? How is it used?
Item 2: why is it using MSEG and not the new HANA table MATDOCU?
Item 3: I think there is an FM for that but in any case even in the conventional ABAP you probably don't need two separate SELECTs. And "inactive" flag in JEST is not considered? That's odd...
Item 4. Not really sure what it's telling us. LEFT JOIN existed in ABAP for a very long time.
The story started with updating an old program. Seems like a valid case but then where is the "before" and "after" code. That would be more beneficial IMHO than what looks like a random collection. I feel you could do much better than this.
Thanks for your feedback. I agree , that the title could have been more meaningful and less confusing .
ABAP is not still ABAP any more.
There are huge number of ABAPers who are yet to get exposure to practical implementation and use of ABAP on HANA features . This blog targets audience those just know new features part of ABAP on HANA but struggle to make use of it in practical scenarios.
There are plenty of other blogs and tutorials about what's CDS, AMDP and Table function . This blog does not intent to describe it.
Item 1: As I said , my intention is not to explain Table function - There a lot available in the community
Item 2 - Its a common misunderstanding. S4H and Suite on HANA are not the same. MATDOC is available only in S4H and not in Suite on HANA. I chose a scenario to explain how can we write it using SQL Script. May be I could have used the cliché SFLIGHT scenario which everyone is fed up with.
Item 3 : Function module ? My topic of interest is code pushdown to DB layer . Not to keep the logic in Application layer. In reality, I use inactive flag in JEST to reduce the project records. As I said before, functionality is not hat I am focusing. Also , are you in a mood to accept a challenge ? write it in a single SELECT using conventional ABAP(I tried and I couldn't) - Will be a great reference to us.
Item 4 : This is just an idea . I haven't seen such a logic written else where( even in SAP's standard programs)- I mean, using a LOJ to achieve the described scenario.
its not about updating an old program - Its about design thinking. There are many people who still write their code using old programming paradigm. Its for them.
Love - Sreehari
Regarding the challenge, I believe either one of the following approaches should work. Note that I'm using table CRM_JEST instead of JEST, just because I had more test data in that one. Let me know if I'm missing anything.
I lost track of the topic already as its been 4 years + .
Useful. Something that I haven't thought before. Looking forward the next one.
I - like Jelena - was a little confused by the title, but I think you are giving quit a few nice examples here!
Thanks a lot!
Thanks man. Any suggestion on a less confusing title ? ?
This is a great blog! It shows the different examples that make sense.
I like the way it was put together. You could have put your entire program and then the new program. That wouldn't be real useful. But what you did is leave us with some Tips and Tricks to make our development easier. Because this is a blog about performance - I would look at the best way to do things. If it doesn't happen to be the best way, that's OK, someone will comment.
Long story - short or short story long - I really liked this. It will be one of my bookmarks. There are some solid ideas presented with how to fix.
Thank you for this new addition to some of my tips and tricks I keep with me!
This comment made my day ! You must have seen the old title - Which was pretty confusing ! Thanks for the titles suggestions too .
And I really thought about explain end to end , how we revamped a conventional abap report to kick-ass Fiori + CDS report.
I am bit worried about the Intellectual property management ( customer policy ) here to publish any content outside . But, I am planning to write a blog in general soon for sure,.
Yes, customer policy drives what we can share and what we can't share.
I'm looking forward to the next blog,
This is a nice blog. You have explained clearly how to handle few scenarios in SQL, rather than doing the same on ABAP.
I have just one suggestion. In the part 3, why can't we use a HAVING clause? I think the outer select is not required.
Thanks Chandan .
Thats right. We can apply HAVING clause and avoid the nested SQL 🙂 thanks for pointing it out.
Awesome post, thanks for this. I exactly get your idea what you targetted and explained in the post. No matter what others have commented, but it of great help for me learning about scenarios where this could be applied. Thanks and keep blogging.