Improving Performance of FOR ALL ENTRIES QUERY

如何提高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)
			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无数据，ｗｈｅｒｅ语句就当没有条件处理；
３、系统自动删除重复纪录；

Trackback: http://tb.blog.youkuaiyun.com/TrackBack.aspx?PostId=1619345