Need help from performance gurus!

[Former Member]

Dear all,

I am face to a problem of performance and I don't know if the solution I took is good or not.

In a program we read a directory and we read each files. Files contain users ID maybe some maybe all table USR02 (ca. 300.000 users).

For each file I need to consult table USR01 / USR21 / ADRP / ADCP / ADR6.

According to you is it better to do a FOR ALL ENTRIES at the begining of a loop (inside the loop for previous tables) or to use SELECT SINGLE on each table during the loop?

I took option of SELECT SINGLE but I do not know if SELECT FOR ALL ENTRIES (for 5 tables) within loop and using after hashed tables would be better or not?

I am lost!

Thanks for your help!

David

[former_member201275]

Quickest is to use an inner join, choosing only the fields you require, like so:

    select usr02~bname
         adrp~title
         adrp~name_first
         adrp~name_last
         adcp~department
         usr02~accnt
         usr21~kostl
         adr6~smtp_addr
         usr02~class
         usr02~gltgv

         usr02~ustyp
         into corresponding fields of table gt_list
         from usr02
              join usr21  on ( usr21~bname     = usr02~bname      )
         left join adrp   on ( adrp~persnumber = usr21~persnumber )
         left join adcp   on ( adcp~addrnumber = usr21~addrnumber
                           and adcp~persnumber = usr21~persnumber )
         left join adr6   on ( adr6~addrnumber = usr21~addrnumber
                           and adr6~persnumber = usr21~persnumber )
        where usr02~bname in r_user.

[Former Member]

AMEN.  SELECT SINGLE inside a loop will perform a database call for every entry in the internal table, separated by application logic.  FOR ALL ENTRIES is marginally better but it still performs the join on the application server, not in the database.  With FAE you will still have a variable number of database calls based on the entries in your internal table.  An inner join, like Glen Anthony suggested, will ensure your program has only one database call no matter the results.

[ThomasZloch]

I understand there can be anything between 1 and 300K single user ID values as selection criteria. In order to avoid short dumps due to maximum size of SQL statement being exceeded, it seems that FAE should be used here for the BNAME selection, in conjunction with the JOIN as suggested by Glen. Both JOIN and FAE can happily coexist in the same statement.

As always, the FAE driver table should be sorted and duplicates removed (and must not be empty )

Thomas

[ronaldo_aparecido]

Hello

1 - You can use select with pakage size:

  SELECT *

               FROM USR01

               INTO TABLE internal_table

               PACKAGE SIZE 5000

               WHERE your condition.

    LOOP AT internal_table INTO workarea.

      APPEND workarea  TO internal_table_aux.

    ENDLOOP.

    REFRESH internal_table[].

  ENDSELECT.

commit work (to clear memory).

loop at internal_table_aux

append to internal_table_aux2

if internal_table_aux2 with 5000 records.

select *

         FROM USR02

         into ...

         your condition

.

.

endloop.

http://wiki.scn.sap.com/wiki/pages/viewpage.action?pageId=72220789

[renatobertizini]

Hi,

You can try do SELECTs for internal tables (using FOR ALL ENTRIES or SELECTs FULLS) and use and READs using field symbols and binary search for create final register.

[freek_cavens2]

Hi David,

I would use a join for the USR01/USR21 tables and then a for all entries on the ADR* tables using hash tables.  Select single inside a loop is very slow due to database traffic.  Read the hash tables assigning them to field symbols, as this is faster than a work area for large tables.  And be sure to make the for all entries table sorted with a unique key (as Thomas suggested).  The for all entries will translate to the database as : where value = '1'

                          or value = '2'

                          or value = '3'

                          ...

splitting the for all entries table in sets of selects on the database with multiple or's.  If the table is not unique, then values will be requested multiple times and slow performance.

A join on all tables could turn out to be quite heavy due to the number of joins required.  You could easily do a benchmark though.  It's doesn't take long to write both programs .  Then you can do a performance analysis.

Regards,

Freek