ABAP的SQL优化方案

sap的数据库中的表，有的达到上千万条的记录，如果对这样的表进行全表的扫描，那是相当费时的，一般只能通过索引来获取数据。

 

然而索引也不能随便创建，因为建立索引需要磁盘空间；而且降低业务的操作效率，在数据表中插入

一条记录，同时在索引表中也要插入一条记录。

 

因此，在写代码时，使用仅有的索引或主键获取数据，然后根据其它的条件，在内表中进行过滤。

 

另外，SQL中只能有一个left join，和Native SQL不同，所以数据一般要分段获取。

 

看代码：

form. collects.
  data:t_ekbed like c_ekbe occurs 0,
       t_ekpo like c_eban occurs 0,
       c_eban1 like c_eban,
       tmp_ekbe like c_ekbe.

  select eban~frgdt eban~banfn eban~bnfpo eban~matnr eban~menge
    eban~meins eban~lfdat eban~afnam eban~ekgrp eban~bednr
    eban~ebeln eban~ebelp
    into corresponding fields of table t_eban
    from eban
    where eban~afnam in p_afnam and eban~frgdt in p_frgdt and    “查询条件已经都在这了
          eban~banfn in p_banfn and eban~matnr in p_matnr and
          eban~ekgrp in p_ekgrp and eban~bednr in p_bednr and
          eban~ekgrp like 'S%' and  eban~LOEKZ <> 'L'.

****还有部分的业务数据在其它表中没有取出来

*******eban~ebeln eban~ebelp将作为关键的条件
  loop at t_eban into c_eban.
    if not c_eban-ebeln is initial.
      c_ebeln-ebeln = c_eban-ebeln.
      c_ebelnlp-ebeln = c_eban-ebeln.
      c_ebelnlp-ebelp = c_eban-ebelp.
      append c_ebeln to t_ebeln.
      append c_ebelnlp to t_ebelnlp.
    endif.
  endloop.
  DELETE ADJACENT DUPLICATEs  FROM t_ebeln.
  DELETE ADJACENT DUPLICATEs  FROM t_ebelnlp.
  if not t_ebelnlp is initial.
    select ekpo~EBELN ekpo~ebelp ekpo~anfnr ekko~aedat ekko~lifnr
      eket~EINDT
      into corresponding fields of table t_ekpo
      from ekpo
      inner join ekko on ekpo~EBELN = ekko~ebeln
      inner join eket on ekpo~ebeln = eket~ebeln and
                        ekpo~ebelp = eket~ebelp
      for all entries in t_ebelnlp
      where ekpo~ebeln = t_ebelnlp-ebeln and
            ekpo~ebelp = t_ebelnlp-ebelp.

 endif.
****实际收货数量 = 收货数量 - 退货******
 if not t_ebelnlp is initial.
   select ekbe~ebeln ekbe~ebelp EKBE~BUDAT ekbe~bwart ekbe~menge
     ekbe~GJAHR  ekbe~belnr  ekbe~BUZEI
     into corresponding fields of table t_ekbe
     from ekbe
     for all entries in t_ebelnlp
     where ekbe~ebeln = t_ebelnlp-ebeln and ekbe~ebelp = t_ebelnlp-ebelp
           and ekbe~vgabe = '1' and ekbe~bwart in ('101','102','122').
   sort t_ekbe by ebeln ebelp.
   loop at t_ekbe into c_ekbe.
     move-corresponding c_ekbe to c_belnr.
     append c_belnr to t_belnr.
     at first.
       xfg = 'x'.
     endat.
     if xfg = 'x'.
        move c_ekbe to tmp_ekbe.
        clear xfg.
        at last.
          xfg = 'x'.
        endat.
        if xfg = 'x'.
          append tmp_ekbe to t_ekbed.
          clear xfg.
        endif.
        continue.
     endif.
     if tmp_ekbe-ebeln = c_ekbe-ebeln and tmp_ekbe-ebelp = c_ekbe-ebelp.
        if c_ekbe-bwart = '102' or c_ekbe-bwart = '122' .
          c_ekbe-menge = 0 - c_ekbe-menge.
        elseif c_ekbe-bwart = '101'.         "实际收货日期
          if tmp_ekbe-belnr > c_ekbe-belnr or
             ( tmp_ekbe-belnr > c_ekbe-belnr and
               tmp_ekbe-buzei > c_ekbe-buzei ).
             tmp_ekbe-budat = c_ekbe-budat.
           endif.
        endif.
        tmp_ekbe-menge = tmp_ekbe-menge + c_ekbe-menge.
     else.
       append tmp_ekbe to t_ekbed.
       move c_ekbe to tmp_ekbe.
     endif.
     at last.
       xfg = 'x'.
     endat.
     if xfg = 'x'.
       append tmp_ekbe to t_ekbed.
       clear xfg.
     endif.
    endloop.
 endif.
********取采购批准日期*******
  if not t_ebeln is initial.
   select OBJECTID  UDATE
     into table t_cdhdr
     from cdhdr
     for all entries in t_ebeln
     where  cdhdr~objectid = t_ebeln-ebeln and BJECTCLAS = 'EINKBELEG'
           and USERNAME = 'PM02' and tcode = 'ME28'.
  endif.
****取WBS********
 if not t_belnr is initial.
   DELETE ADJACENT DUPLICATEs  FROM t_belnr.

*****使用for all entries 提高性能，使用mseg的主键
   select  mseg~mjahr mseg~mblnr mseg~ZEILE mseg~ebeln mseg~ebelp
           PRPS~POSID
     into table t_mseg
     from mseg
     inner join prps on mseg~MAT_PSPNR = prps~PSPNR
     for all entries in t_belnr
     where mseg~mjahr = t_belnr-gjahr and mseg~mblnr = t_belnr-belnr and
           mseg~ZEILE = t_belnr-buzei.
  endif.
*****数据连接******
**   t_eban-ebeln = t_ekbed-ebeln and t_eban-ebelp = t_ekbed-ebelp
***  t_eban-ebeln = t_mseg-ebeln and t_eban-ebelp = t_mseg-ebelp
  sort t_ekbed by ebeln ebelp.
  sort t_mseg by ebeln ebelp.
  sort t_cdhdr by objectid.
   sort t_ekpo by ebeln ebelp.
  clear tb_eban.
  loop at t_eban into c_eban.
     read table t_ekpo into c_eban1 with key ebeln = c_eban-ebeln
                                            ebelp = c_eban-ebelp
                                            binary search.
     if sy-subrc = 0.
      c_eban-anfnr = c_eban1-anfnr.
      c_eban-aedat = c_eban1-aedat.
      c_eban-lifnr = c_eban1-lifnr.
      c_eban-EINDT = c_eban1-eindt.
     endif.
     read table t_ekbed into c_ekbe with key ebeln = c_eban-ebeln
                                              ebelp = c_eban-ebelp
                                              binary search.
     if sy-subrc = 0.
       c_eban-rmenge = c_ekbe-menge.
       c_eban-budat = c_ekbe-budat.
     endif.
     read table t_mseg into c_mseg with key ebeln = c_eban-ebeln
                                              ebelp = c_eban-ebelp
                                              binary search.
     if sy-subrc = 0.
       c_eban-posid = c_mseg-posid.
     endif.

     read table t_cdhdr into c_cdhdr with key bjectid = c_eban-ebeln
                                              binary search.
     if sy-subrc = 0.
       c_eban-udate = c_cdhdr-udate.
     endif.
     select single maktx into c_eban-maktx
       from makt
       where matnr = c_eban-matnr and spras = '1'.
     append c_eban to tb_eban.
  endloop.
endform.                    " collects

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26168649/viewspace-704806/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26168649/viewspace-704806/