Improving Performance of FOR ALL ENTRIES QUERY

最新推荐文章于 2023-07-20 01:16:17 发布

原创最新推荐文章于 2023-07-20 01:16:17 发布 · 3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#performance #query #duplicates #table #delete #sorting

19 SAP Performance 专栏收录该内容

12 篇文章

订阅专栏

本文介绍如何通过排序和删除重复记录来优化ABAP中FOR ALLENTRIES语句的性能，展示了具体实现方法及前后性能对比。

如何提高FOR ALL ENTRIES的查询性能主要从两个方面入手
1 对FOR ALL ENTRIES的驱动表按比较关键字排序;

2 删除FOR ALL ENTRIES的驱动表中比较关键字排序;

REPORT ZEXIT_HELP no standard page heading.
DATA: it_mara TYPE STANDARD TABLE OF mara,
wa_mara TYPE mara,
it_makt TYPE STANDARD TABLE OF makt,
wa_makt TYPE makt,
it_temp_mara TYPE STANDARD TABLE OF mara,
wa_temp_mara TYPE mara.
* Get all the records from MARA
SELECT * FROM mara
INTO TABLE it_temp_mara.
IF sy-subrc = 0.
IF it_temp_mara[] IS NOT INITIAL.
* Duplicate the driver table with the data
    do 2 times.
      append lines of it_Temp_mara to it_mara.
    enddo.
    IF it_mara[] IS NOT INITIAL.
* Select MAKT
      perform select_makt.
*After Sorting
      sort it_mara by matnr.
      perform select_makt.
* After deleting duoplicateentries
      DELETE ADJACENT DUPLICATES FROM it_mara COMPARING matnr.
      perform select_makt.
    ENDIF.
ENDIF.
ENDIF.
*&---------------------------------------------------------------------*
*& Form select_makt
*&---------------------------------------------------------------------*
* Select data friom MAKT
*----------------------------------------------------------------------*
form select_makt .
DATA: t1 TYPE i,
t2 TYPE i,
tmin TYPE i.
refresh it_makt[].
GET RUN TIME FIELD t1.
SELECT *
FROM makt
INTO TABLE it_makt
FOR ALL ENTRIES IN it_mara
WHERE matnr = it_mara-matnr.
GET RUN TIME FIELD t2.
tmin = t2 - t1.
tmin = tmin .
WRITE:/ ' Time(ms) = ', tmin.
Endform.                    "select_makt

测试结果

No of run	Records in MARA	Records in MAKT	Time required ( mili-seconds)
No of run	Records in MARA	Records in MAKT	Before sort	After Sort	After sort and delete adjacent duplicates
1	12053	71907	4040.26	3373.738	2017.051
2	12053	71907	4784.953	3387.241	2007.644
Average Time

几个注意点：
１、有FOR ALL ENTRIES IN itab的不能使用order by；
２、如果 itab无数据，ｗｈｅｒｅ语句就当没有条件处理；
３、系统自动删除重复纪录；

关于ranges和FOR ALL ENTRIES IN的使用大家可以参阅SAP的标准代码的使用方法，代码参见函数MRM_INVHEAD_MANY_READ，摘抄如下：

SORT t_lifnr.
DELETE ADJACENT DUPLICATES FROM t_lifnr.
DESCRIBE TABLE t_lifnr LINES count.
if count < 5000.
      SELECT * FROM rbkp INTO TABLE t_rbkp
                     WHERE belnr IN t_belnr
                     AND   gjahr IN t_gjahr
                     AND   bukrs IN t_bukrs
                     AND   bldat IN t_bldat
                     AND   budat IN t_budat
                     AND   lifnr IN t_lifnr
                     AND   ivtyp IN rg_ivtyp.
    else.
      SELECT * FROM rbkp INTO TABLE t_rbkp
                     FOR ALL ENTRIES IN t_lifnr
                     WHERE lifnr = t_lifnr-low
                     AND   belnr IN t_belnr
                     AND   gjahr IN t_gjahr
                     AND   bukrs IN t_bukrs
                     AND   bldat IN t_bldat
                     AND   budat IN t_budat
                     AND   ivtyp IN rg_ivtyp.
endif.