Skip to Content

ABAP Optimize a loop and select to database

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.
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Nov 30, 2017 at 10:13 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.
  • Nov 30, 2017 at 02:09 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 01, 2017 at 08:57 AM

    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!

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 30, 2017 at 04:40 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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