Horrible Performance, HANA push down to the rescue

sreehari_vpillai · ‎03-18-2018

Introduction

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"

Motive

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:

Output :

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.

AMDP - What's happening

Title credit : c436ae948d684935a91fce8b976e5aa7

You see this message because you read the whole blog. care to leave a comment and like the blog?

Sreehari V Pillai