Code Push down to AMDP versus Open SQL in ABAP code

[former_member185171]

Hi,

we are experimenting with code-pushdown to AMDP and found a good example in SLCM to try this out.

We already managed to speed up the processing In ABAP with Open SQL and all kind of new ABAP-functionality.

The idea was to make it work still faster in AMDP. But up to now the result is rather negative.

What we want to do is replace a simple evaluation path in HR/PD, by reading relations in a loop until there are no more underlying objects - this is what function module RH_STRUC_GET does, but that is a lot slower.

The ABAP solution reduced the response from several minutes to about 0.6 seconds. The AMDP needs approximately 0.9 seconds (150 % but still fast).

Is there a way to find out where the AMDP 'loses time'? As far as I know I can't run a trace on it. Or can I?

Furthermore: are there other possibilities to copy tables in an AMDP apart from the SELECT with a UNION?

Below is how I wrote it. The logic is ok and exactly the same as in ABAP. Where do I lose time?

*-- Start with 1 record only

    lt_o = SELECT 'O' as otype,

                  objid as objid,

                  lv_level as level

           from :im_objid ;

    SELECT count( * ) into lv_o from :lt_o;

*-- Loop

    WHILE lv_o <> 0 DO

       lv_level = :lv_level + 1 ;

*-- Read data from the database
       lt_object = SELECT sclas AS otype,

                          sobid AS objid,

                          lv_level as level

              FROM hrp1001 as hrp1001

              inner join :lt_o as input

                 on hrp1001.otype = input.otype

                 AND hrp1001.objid = input.objid

              WHERE hrp1001.begda <= :im_endda

                AND hrp1001.endda >= :im_begda

                AND ( ( hrp1001.subty = 'B002' AND hrp1001.sclas = 'O' )

                   OR ( hrp1001.subty = 'A501' AND hrp1001.sclas = 'SM' ) )

                AND hrp1001.mandt =  session_context('CLIENT')

                AND hrp1001.plvar = '01'

                AND hrp1001.istat = '1';

*-- Are there any 'O'-s left for a next loop?

       lt_o = SELECT otype,

                     objid,

                     level

                 from :lt_object

                 where otype = 'O';

       SELECT count( * )

              into lv_o

              from :lt_o;

*-- Add the 'SM'-s to the output

       rt_sm = SELECT *

                  from :rt_sm

               UNION

               SELECT *

                  from :lt_object

                  where otype = 'SM' ;

*-- ... and start allover again.

    END WHILE ;

I hope anyone can help me out.

Regards.

Kris

[thomasgauweiler · Accepted solution]

I am not an expert for SQL Script. But what I sometimes hear, is that sequential code like your WHILE loop prevents optimization. Also ensure that your AMDP is marked with OPTIONS READ-ONLY.

Regards, Thomas

[former_member185171 · Accepted solution]

Thomas,

the influence of the sequential code is reasonable. It is the same in ABAP Open SQL. That's the logic.

Thanks a lot.

Kris