drad 读取优化过程

1 开始 abap语句是这样的
    SELECT SINGLE drad~dokar drad~doknr drad~doktl
      INTO (drad-dokar ,
            drad-doknr ,
            drad-doktl)
    FROM drad
    INNER JOIN draw
              ON drad~dokar = draw~dokar AND
                 drad~doknr = draw~doknr AND
                 drad~dokvr = draw~dokvr AND
                 drad~doktl = draw~doktl
    WHERE drad~dokob = 'MARA' AND
          drad~objky = <f_data>-matnr AND
          draw~loedk = '' .

通过跟踪，发现他最慢
2 第一次优化
    EXEC SQL.
      select drad.dokar, drad.doknr, drad.doktl
       from drad inner join draw on
         drad.mandt = draw.mandt and
         drad.dokar = draw.dokar and
         drad.doknr = draw.doknr and
         drad.dokvr = draw.dokvr and
         drad.doktl = draw.doktl
       where rownum = 1
       and draw.loedk = ' '
       and drad.mandt = '300'
       and drad.dokob = 'MARA'
       AND drad.objky = :<f_data>-matnr

       into :wa_drad
    ENDEXEC.
    if ( sy-subrc = 0 ) and ( sy-dbcnt = 1 ).
      MOVE-CORRESPONDING wa_drad to drad.
    endif.
速度并没有提升什么
3 再优化
CLEAR: drad,wa_drad.
    REFRESH :it_drad,it_doc.

    SELECT *
      FROM drad
      INTO  TABLE it_doc
      WHERE
      drad~dokob = 'MARA' AND
          drad~objky = <f_data>-matnr.
    IF sy-subrc = 0.
      SELECT  dokar doknr doktl
      INTO TABLE it_drad
      FROM draw
        FOR ALL ENTRIES IN it_doc
        WHERE draw~loedk EQ space
        AND dokar = it_doc-dokar
        AND doknr = it_doc-doknr
        AND dokvr = it_doc-dokvr
        AND doktl = it_doc-doktl.
    ENDIF.
    READ TABLE it_drad INTO wa_drad INDEX 1.

速度明显提升

4 仔细想了一下，有了最终的语句

SELECT SINGLE drad~dokar drad~doknr drad~doktl drad~vobj
      INTO (drad-dokar ,
            drad-doknr ,
            drad-doktl ,
            drad-vobj)
    FROM drad
    INNER JOIN draw
              ON drad~dokar = draw~dokar AND
                 drad~doknr = draw~doknr AND
                 drad~dokvr = draw~dokvr AND
                 drad~doktl = draw~doktl
    WHERE
      draw~loedk = '' AND
          drad~objky = <f_data>-matnr AND
       drad~dokob = 'MARA' .

效果不错

 

 

 

 

 

 

   1. 首先从业务逻辑分析,让顾问尽量缩减处理数据量(比如条件必输,或者其他限制等等), 可以有效减少abaper工作量
   2. 不能改变原程序逻辑
   3. 避免循环中嵌套SQL语句
   4. 绝对避免select…endselect循环处理
   5. 避免select *
   6. 使用for all entries注意: 条件表不能为空, 删除条件表中重复的记录
   7. 数据量庞大可以使用hashed表(本程序没有使用是因为逻辑复杂,并且用户接受最终调试结果,否则…..俺要进一步了)