使用SELECT 和OPEN CURSOR 读取big table的性能比较

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.