一个逆向思维的优化SQL的方法

ksaponka

REPORT z_scm_gr_his .
TABLES: ekko,ekbe.
CONSTANTS : once TYPE i VALUE 100000.
TYPES: BEGIN OF ty_ekbe ,
         ebeln LIKE ekbe-ebeln ,
         ebelp LIKE ekbe-ebelp ,
         dmbtr LIKE ekbe-dmbtr ,
         bewtp LIKE ekbe-bewtp , "E Q
         shkzg LIKE ekbe-shkzg , "借贷
         arewr LIKE ekbe-arewr ,
       END OF ty_ekbe.
DATA: hs_ekbe TYPE HASHED TABLE OF ty_ekbe
        WITH UNIQUE KEY ebeln ebelp bewtp shkzg WITH HEADER LINE .
DATA: wa LIKE LINE OF hs_ekbe.
TYPES: BEGIN OF ty_ekbe_sum ,
         mandt LIKE sy-mandt ,
         ebeln LIKE ekbe-ebeln ,
         ebelp LIKE ekbe-ebelp ,
*         dmbtr LIKE ekbe-dmbtr ,
       END OF ty_ekbe_sum.
DATA: hs_ekbe_sum TYPE HASHED TABLE OF ty_ekbe_sum
        WITH UNIQUE KEY mandt ebeln ebelp WITH HEADER LINE .
DATA: itab_ekbe_sum TYPE STANDARD TABLE OF ty_ekbe_sum
      WITH HEADER LINE .
DATA: wa_sum LIKE LINE OF hs_ekbe_sum.
DATA: BEGIN OF itab_gr OCCURS 0 ,
        belnr LIKE ekbe-belnr ,
        ebeln LIKE ekbe-ebeln ,
        ebelp LIKE ekbe-ebelp ,
      END OF itab_gr .
DATA: tmpebeln LIKE ekpo-ebeln.
DATA: n TYPE i ,
      m LIKE sy-tabix.
DATA: linenum  LIKE sy-tabix,
      fromnum  LIKE sy-index,
      tonum    LIKE sy-index.
DATA :
  v_t1    TYPE i,
  v_t2    TYPE i,
  v_time  TYPE i.
SELECT-OPTIONS: s_budat FOR ekbe-budat .
PARAMETERS:     mode .
START-OF-SELECTION.
  SELECT ekbe~ebeln ekbe~ebelp bewtp shkzg dmbtr arewr
  INTO CORRESPONDING FIELDS OF wa
  FROM ekbe
  WHERE ekbe~bewtp IN ('E','Q') AND
        ekbe~budat IN s_budat .
    IF wa-bewtp = 'Q' .
      wa-dmbtr = 0 - wa-arewr .
      wa-bewtp = 'E'.
    ENDIF.
    IF wa-shkzg = 'H'.
      wa-dmbtr = 0 - wa-dmbtr .
      wa-shkzg = 'S'.
    ENDIF.
    COLLECT wa INTO hs_ekbe.
  ENDSELECT.
*  DELETE hs_ekbe_sum WHERE dmbtr = 0.
  DELETE hs_ekbe WHERE dmbtr = 0.
  LOOP AT hs_ekbe INTO wa .
    MOVE-CORRESPONDING wa TO wa_sum.
    wa_sum-mandt = sy-mandt .
    COLLECT wa_sum INTO hs_ekbe_sum .
  ENDLOOP.
  IF mode = '1'.
    GET RUN TIME FIELD v_t1.
    SELECT belnr ebeln ebelp INTO TABLE itab_gr
    FROM ekbe
    FOR ALL ENTRIES IN hs_ekbe
    WHERE ebeln = hs_ekbe-ebeln AND
          ebelp = hs_ekbe-ebelp .
    GET RUN TIME FIELD v_t2.
    v_time = v_t2 - v_t1.
    WRITE v_time.
  ELSE.
    GET RUN TIME FIELD v_t1.
    EXEC SQL.
      TRUNCATE TABLE ZSCM_GR_PO_TMP
    ENDEXEC.
**********************插入方法一
*    CLEAR n.
*    LOOP AT hs_ekbe.
*      m = m + 1 .
*      EXEC SQL.
*        INSERT INTO ZSCM_GR_PO_TMP (MANDT,EBELN,EBELP)
*          VALUES (:SY-MANDT,
*                  :hs_ekbe-ebeln,
*                  :hs_ekbe-ebelp)
*      ENDEXEC.
*      n = m MOD once.
*      IF n = 0.
*        n = 0 .
*        EXEC SQL.
*          commit work
*        ENDEXEC.
*      ENDIF.
*    ENDLOOP.
*    EXEC SQL.
*      commit work
*    ENDEXEC.
*******************************方法二，优化Insert
    DESCRIBE TABLE hs_ekbe_sum LINES linenum.
    n = linenum DIV once.
    n = n + 1.
    DO n TIMES.
      itab_ekbe_sum[] = hs_ekbe_sum[].
      fromnum = sy-index * once + 1 .
      DELETE itab_ekbe_sum FROM fromnum TO linenum.
      IF sy-index > 1 .
        tonum = fromnum - once - 1 .
        DELETE itab_ekbe_sum FROM 1 TO tonum.
      ENDIF.
      INSERT zscm_gr_po_tmp FROM TABLE itab_ekbe_sum .
      COMMIT WORK.
    ENDDO.
    SELECT belnr ekbe~ebeln ekbe~ebelp INTO TABLE itab_gr
    FROM ekbe INNER JOIN zscm_gr_po_tmp ON
                ekbe~ebeln = zscm_gr_po_tmp~ebeln AND
                ekbe~ebelp = zscm_gr_po_tmp~ebelp.
    GET RUN TIME FIELD v_t2.
    v_time = v_t2 - v_t1.
    WRITE v_time.
  ENDIF.
  SORT itab_gr BY belnr.
  DELETE ADJACENT DUPLICATES FROM itab_gr COMPARING belnr.


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u1/48634/showart_381980.html