CODEWITHSUNDEEP
sqloracle-databaseoptimizationabap
praethorian
praethorian

Reputation: 812

Need to optimize a simple loop over 200,000 records

I am wondering if anyone can optimize following code:

LOOP AT dispinstock.
  SELECT matnr SUM( gesme ) SUM( verme ) SUM( einme ) SUM( ausme )
         INTO (dispinstock-matnr, dispinstock-gesme,
               dispinstock-verme, dispinstock-einme, dispinstock-ausme)
         FROM lqua
         WHERE matnr = dispinstock-matnr
         AND lgnum = 'xxxxx'
         AND ( lgtyp IN zsd_t301_n
          OR ( lgtyp >= '900' AND lgtyp <= '903' ) )
          GROUP BY matnr.
    MODIFY dispinstock.
  ENDSELECT.
ENDLOOP.

dispinstock 170.000 records,

LQUA 210.000 records (will be larger > 1.500.000 records soon)

This loop take more than 3 minutes. Would it be better to use hashed table instead? Any help or idea would be appreciated.

Upvotes: 4

Views: 1112

Answers (4)

Alex C
Alex C

Reputation: 1142

You should also think about using a field symbol rather than using the modify.

field-symbols: <dispinstock> like line of dispinstock.
loop at dispinstock assigning <dispinstock>.
  " some work
  <dispinstock>-gesme = new value..
  "...
 endloop

This way you reduce the number of times you read the dispinstock table and change the value directly.

Upvotes: 4

Dave Costa
Dave Costa

Reputation: 48121

If the line that says "MODIFY dispinstock" means "update a row in the dispinstock table with the values that were just acquired from the SELECT", then you could probably replace the LOOP and the SELECT with a single MERGE statement.

Something like

MERGE INTO dispinstock
USING (  SELECT matnr, SUM( gesme ) gesme,  SUM( verme ) verme, SUM( einme ) einme,  SUM( ausme ) ausme
         FROM lqua
         WHERE lgnum = 'xxxxx'
         AND ( lgtyp IN zsd_t301_n
          OR ( lgtyp >= '900' AND lgtyp <= '903' ) )
          GROUP BY matnr
      ) lqua
ON lqua.matnr = dispinstock.matnr
WHEN MATCHED THEN UPDATE SET
  gesme = l.gesme, verme = l.verme, einme = l.einme, ausme = l.ausme

Upvotes: 0

Serge
Serge

Reputation: 1

I´m sure that your internal table dispinstock does not contain 170.000 distinct materials! So I suggest to build a table of distinct MATNRs and start the select with a FOR ALL ENTRIES IN ...

... AND ( lgtyp IN zsd_t301_n OR ( lgtyp >= '900' AND lgtyp <= '903' ) )

Insert one row into your range object zsd_t301_n and delete the OR statement

Sign OPTION LOW HIGH

I BT 900 903

Upvotes: 0

Bryan Cain
Bryan Cain

Reputation: 1776

Take the select out of the loop - pull all the data you need from lqua into a separate internal table in a single select statement. Then do a read on the second table inside the loop. Use a hash/sorted table or use a binary search.

Upvotes: 7

Related Questions