ABAP Optimize a loop and select to database

[moya_roland]

Hello guys,

I need to select actives employees from the database and then select the 2 last rows of every employee from infotypes 0000, 0001 and 0008. I'm doing this looping the table that contains the actives employees and for each pernr i'm doing a select up to 2 rows from those infotypes. I know this way is terrible for performance but i can't figure out a better way to achieve what i need. How can i optimize this procedure?

This is the code:

SELECT * FROM PA0000 APPENDING TABLE us_P0000 WHERE endda eq '99991231' AND stat2 eq state or stat2 eq '2'.

  LOOP AT us_p0000 INTO wa_p0000.

  SELECT * FROM PA0000 UP TO 2 ROWS APPENDING TABLE LS_P0000 WHERE pernr eq wa_p0000-pernr ORDER BY endda DESCENDING.

  SELECT * FROM PA0001 UP TO 2 ROWS APPENDING TABLE LS_P0001 WHERE pernr eq wa_p0000-pernr ORDER BY endda DESCENDING.

  SELECT * FROM PA0008 UP TO 2 ROWS APPENDING TABLE LS_P0008 WHERE pernr eq wa_p0000-pernr ORDER BY endda DESCENDING.

  ENDLOOP.

[umayaraj]

hi Rolando Moya Lebron,

change your query little bit :

1) don't use select *

2) use select ebeln eadat from ekko into table itab where

3) don't use loop inside the select query

4) use for all entries to fill internal table and use loop inside read query

[moya_roland]

thanks for your answer,

How can i loop the last 2 rows for each pernr in the internal tables?.

[umayaraj]

hi,

  SELECT * FROM pa0000 APPENDING TABLE us_p0000 WHERE endda EQ '99991231' AND stat2 EQ state OR stat2 EQ '2'.

  SELECT * FROM pa0001 INTO TABLE ls_p0001 FOR ALL ENTRIES IN us_p0000 WHERE  pernr EQ us_p0000-pernr.

  SELECT * FROM pa0008 INTO TABLE ls_p0008 FOR ALL ENTRIES IN us_p0000 WHERE  pernr EQ us_p0000-pernr.

  LOOP AT us_p0000 INTO wa_p0000.

    wa_final- = wa_poo1- .

    READ TABLE ls_p0001 INTO wa_poo1 WITH KEY pernr = wa_p0000-pernr.

    wa_final-perner = wa_poo1-perner.

    READ TABLE ls_p0008 INTO wa_poo8 WITH KEY pernr = wa_p0000-pernr.

  ENDLOOP.

try like this code

[Former Member]

You can try these queries in below way:

1. US_P0000 Structure should only have only pernr field

SELECT DISTINCT pernr FROM PA0000 INTO TABLE US_P0000 where <condition (try to pass keys)>

IF SY-SUBRC EQ 0.

SELECT <REQUIRED FIELDS, NOT ALL> FROM PA0001 UP TO 2 ROWS INTO TABLE LS_PA0000

FOR ALL ENTRIES IN US_P0000

WHERE PERNR EQ US_P0000-PERNR.

<Same for other tables>

ENDIF.

[moya_roland]

Thanks for your answer, i've done as you said but it's just getting only 2 rows for each internal tables not 2 rows per pernr.

Here's the code:

SELECT DISTINCT pernr 
   FROM PA0000 INTO TABLE us_p0000 
   WHERE endda eq '99991231' AND stat2 eq state or stat2 eq '2'.

   IF sy-subrc eq 0.

    SELECT pernr begda endda aedtm massn massg stat2

      FROM pa0000 UP TO 2 ROWS INTO TABLE ls_p0000

      FOR ALL ENTRIES IN us_p0000

      WHERE pernr eq us_p0000-pernr.



    SELECT pernr begda endda aedtm orgeh kostl plans stell abkrs btrtl bukrs

      FROM pa0001 UP TO 2 ROWS INTO TABLE ls_p0001

      FOR ALL ENTRIES IN us_p0000

      WHERE pernr eq us_p0000-pernr.



    SELECT pernr begda endda aedtm bet01 TRFGR

      FROM pa0008 UP TO 2 ROWS INTO TABLE ls_p0008

      FOR ALL ENTRIES IN us_p0000

      WHERE pernr eq us_p0000-pernr.

   ENDIF.

[roberto_vacca2]

Hi.

With "endda eq '99991231' AND stat2 eq state or stat2 eq '2'." you're saying that you don't mind the endda value if STAT2 equals 2, is it right?

Then if you're using UP TO 2 ROWS , you'll always get 2 rows, even though you have 1 billions record in us_p0000.

"Example for you

SELECT * FROM mara UP TO 10000 ROWS INTO TABLE @DATA(ft_mara).
SELECT * FROM makt UP TO 2 ROWS INTO TABLE @DATA(ft_makt)
  FOR ALL ENTRIES IN @ft_mara
  WHERE matnr = @ft_mara-matnr AND
       spras EQ @sy-langu.<br>

FT_MAKT will have only 2 records.

What's your goal?

Hope to help, bye

[moya_roland]

Hi Roberto,

My goal is to get 2 rows rows from pa0001 for each pernr key in us_p0000.

[Former Member]

I think your first query should be like this:

SELECT DISTINCT pernr 
   FROM PA0000 INTO TABLE us_p0000 
   WHERE endda eq '99991231' AND ( stat2 eq state or stat2 eq '2' ).

You cannot write direct select query to fetch 2records per pernr, may be you have to write subquery..
Let me know if you are still stuck then I can try query in my system and let you know.

[pokrakam]

Depending on your data distribution and volume, it could well be more efficient to fetch all in a single JOINed SELECT and remove the extra info.

SELECT ... from PA0000 outer join PA0001 outer join PA0008

Then process the internal table to remove the records you don't need. It's a tradeoff between data volume and number of queries. A true performance comparison is only possible with realistic volumes, so at the very least a test program in QA. I think there's a reasonably good chance this is faster.

[moya_roland]

Hi,

It's big volume of data. More than 10k of rows per table.

[pokrakam]

So you'll fetch a couple of MB once instead of 30,001 trips to the database and back. Sounds a better option to me...

[Former Member]

I see you are using the old OpenSQL syntax, but maybe that is just by habit? If you are on a HANA db, and are able to use AMDP, you can use the RANK functionality for this.

Here is an incomplete example just to show the concept of ranking:

select pernr, begda, endda,
       rank() over (partition by pernr order by endda desc ) as rank
  from pa0002
  where rank <= 2 

I dont think this is possible in ABAP OpenSQL or CDS views, but please correct me if it has been added there as well!