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