Geek Answers Handbook

Need to optimize a simple cycle of over 200,000 records

I am wondering if anyone can optimize the 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 entries,

LQUA 210,000 entries (there will be more> 1,500,000 entries coming soon)

This cycle takes more than 3 minutes. Would it be better to use a hash table instead? Any help or idea would be appreciated.

+4
source share
4 answers

Take the selection from the loop - pull all the necessary data from lqua into a separate internal table in one select statement. Then do the reading in the second table inside the loop. Use a hash / sort table or use a binary search.

+7
source

You should also consider using a field symbol, not a change.

 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.

+4
source

I am sure that your internal dispinstock table does not contain 170,000 different materials! Therefore, I suggest creating a table of various MATNRs and starting the selection with ALL RECORDS IN ...

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

Insert one line into the zsd_t301_n range object and delete the OR operator

Sign OPTION LOW HIGH

I BT 900 903

0
source

If a line labeled “MODIFY dispinstock” means “update the row in the dispinstock table with the values ​​that were just obtained from SELECT,” then you might replace LOOP and SELECT with a single MERGE statement.

Sort of

 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
0
source

More articles:


All Articles