FOR ALL ENTRIES IN 影响速度
财务一直抱怨与客户发票对帐的报表慢,公司流程是根据出货开发票,所以在此报表中有取销售订单相关信息,所以有交货与销售订单关联取数据,开始我是这样处理的
*get sales order info
select
A~VBELN "销售订单号
A~POSNR "销售行项目
A~KBMENG "数量
B~KNUMV "条件码
B~FAKSK "冻结码
B~AUART "订单类型
B~ERDAT "订单日期
A~NETPR "净价
A~WAERK "工厂
A~KPEIN "销售单位
C~VBELN AS SONUM "交货单
C~POSNN "交货行项目
D~BEZEI "交货单类型
E~BSTKD "客户定单号
INTO CORRESPONDING FIELDS OF TABLE I_SD
FROM VBAK AS B INNER JOIN VBAP AS A
ON B~VBELN = A~VBELN
INNER JOIN VBFA AS C
ON A~VBELN = C~VBELV AND A~POSNR = C~POSNV
INNER JOIN TVAKT AS D
ON B~AUART = D~AUART
INNER JOIN VBKD AS E
ON E~VBELN = A~VBELN
FOR ALL ENTRIES IN I_DH
WHERE "C~VBELN = I_DH-VBELN AND C~POSNN = I_DH-POSNR and
( C~VBTYP_N = ’J’ OR C~VBTYP_N = ’H’ OR C~VBTYP_N = ’T’ ).
后来我用ST05跟踪发现这个语句居然要用四分钟,其实一共也才20000条交货记录,后来到到SQL语句里面一看,原来这一句被系统分成了一万多条,而且每一条都执行成红色时间,晕倒之余,我用另一种方法让它不分解,先读出所有记录,然后再来删除
select
A~VBELN "销售订单号
A~POSNR "销售行项目
A~KBMENG "数量
B~KNUMV "条件码
B~FAKSK "冻结码
B~AUART "订单类型
B~ERDAT "订单日期
A~NETPR "净价
A~WAERK "工厂
A~KPEIN "销售单位
C~VBELN AS SONUM "交货单
C~POSNN "交货行项目
D~BEZEI "交货单类型
E~BSTKD "客户定单号
INTO CORRESPONDING FIELDS OF TABLE I_SD
FROM VBAK AS B INNER JOIN VBAP AS A
ON B~VBELN = A~VBELN
INNER JOIN VBFA AS C
ON A~VBELN = C~VBELV AND A~POSNR = C~POSNV
INNER JOIN TVAKT AS D
ON B~AUART = D~AUART
INNER JOIN VBKD AS E
ON E~VBELN = A~VBELN
WHERE C~VBTYP_N = ’J’ OR C~VBTYP_N = ’H’ OR C~VBTYP_N = ’T’ .
LOOP AT I_SD.
READ TABLE I_DH WITH KEY VBELN = I_SD-SONUM
POSNR = I_SD-POSNN.
IF SY-SUBRC <> 0.
_delete I_SD.
ENDIF.
ENDLOOP.
十秒!最后只用了十秒!再次晕倒!
总结:数据量小的时候可以用 FOR ALL ENTRIES IN ,因为系统里面的处理就像两个select语句循环,唉!我受苦了!分享给大家,希望大家不要再受苦了!