需要优化超过 200,000 条记录的简单循环
我想知道是否有人可以优化以下代码：
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 条记录，
LQUA 210.000 条记录（很快就会更大> 1.500.000 条记录）
这个循环需要超过3分钟。 使用哈希表会更好吗？ 任何帮助或想法将不胜感激。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将 select 从循环中取出 - 在单个 select 语句中将您需要的所有数据从 lqua 提取到单独的内部表中。然后在循环内读取第二个表。使用哈希/排序表或使用二分搜索。
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.
您还应该考虑使用字段符号而不是使用修改。
这样您就可以减少读取 dispinstock 表并直接更改值的次数。
You should also think about using a field symbol rather than using the modify.
This way you reduce the number of times you read the dispinstock table and change the value directly.
我确信您的内部表 dispinstock 不包含 170.000 种不同的材料！
开始选择
因此，我建议建立一个不同 MATNR 的表，并以 FOR ALL ENTRIES IN ... ... 。
AND ( lgtyp IN zsd_t301_n OR ( lgtyp >= '900' AND lgtyp <= '903' ) )
在范围对象 zsd_t301_n 中插入一行并删除 OR 语句
Sign OPTION LOW HIGH
I BT 900 903
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
如果“MODIFY dispinstock”行的意思是“用刚刚从 SELECT 获取的值更新 dispinstock 表中的一行”，那么您可能可以用单个 MERGE 语句替换 LOOP 和 SELECT。
像这样的东西
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