Created by Jerry Wang on May 06, 2014
对于包含庞大数目的database table的读访问,可以采用SELECT 一次性把所有数据全部读取至internal table及使用OPEN CURSOR 按package size 分块读取两种方式。
当comm_product包含如下图所示的number of records时,
用下列report测试发现不管package size指定成100,1000或是10000,两种方式就执行时间来说非常接近。
再来比较memory consumption:
在执行solution1之前创建一个memory snapshot:
solution2执行完毕之后再次创建一个memory snapshot:
使用tcode S_MEMORY_INSPECTOR比较两个snapshot:
[外链图片转存失败(img-pD0vympq-1562038087646)(https://user-images.githubusercontent.com/5669954/27251108-f08450c8-533f-11e7-9206-7fa5ae0a090c.png)]
比较显示solution2的内存占用远远小于solution1, 因为solution1需要使用internal table存放378万条product的数据,而solution2由于是分批处理,其internal table只需存储数量等于package size个product的信息。
PARAMETERS: pac type i OBLIGATORY DEFAULT 100. data: lt_product TYPE STANDARD TABLE OF comm_product, lt_name1 TYPE STANDARD TABLE OF comm_product-upname, lt_name2 TYPE STANDARD TABLE OF comm_product-upname, lv_start TYPE i, lv_end type i. data: ll type SORTED TABLE OF string WITH UNIQUE KEY table_line. FORM solution1. GET RUN TIME FIELD lv_start. SELECT * INTO TABLE lt_product FROM comm_product. LOOP AT lt_product ASSIGNING FIELD-SYMBOL(<product>). INSERT <product>-upname INTO TABLE lt_name1. ENDLOOP. GET RUN TIME FIELD lv_end. lv_end = lv_end - lv_start. WRITE: / 'Solution1: ' , lv_end COLOR COL_NEGATIVE. ENDFORM. FORM solution2. DATA:lv_cursor TYPE cursor, lt_selection TYPE STANDARD TABLE OF comm_product. GET RUN TIME FIELD lv_start. OPEN CURSOR lv_cursor FOR SELECT * FROM comm_product. DO. FETCH NEXT CURSOR lv_cursor INTO TABLE lt_selection PACKAGE SIZE pac. IF sy-subrc NE 0. CLOSE CURSOR lv_cursor. EXIT. ENDIF. LOOP AT lt_selection ASSIGNING FIELD-SYMBOL(<product2>). INSERT <product2>-upname INTO TABLE lt_name2. ENDLOOP. CLEAR: lt_selection. FREE: lt_selection. ENDDO. GET RUN TIME FIELD lv_end. lv_end = lv_end - lv_start. WRITE: / 'Solution2: ' , lv_end COLOR COL_NEGATIVE. ENDFORM. START-OF-SELECTION. PERFORM solution1. PERFORM solution2. ASSERT lt_name1 = lt_name2.