FOR ALL ENTRIES IN 影响速度(转贴-http://blog.vsharing.com/yangzg/ )

FOR ALL ENTRIES IN 影响速度

  财务一直抱怨与客户发票对帐的报表慢,公司流程是根据出货开发票,所以在此报表中有取销售订单相关信息,所以有交货与销售订单关联取数据,开始我是这样处理的

*get sales order info
  select
            A~VBELN        "销售订单号
            A~POSNR       "销售行项目
            A~KBMENG     "数量
            B~KNUMV       "条件码
            B~FAKSK        "冻结码
            B~AUART        "订单类型
            B~ERDAT        "订单日期
            A~NETPR        "净价
            A~WAERK  "工厂
            A~KPEIN        "销售单位
            C~VBELN AS SONUM        "交货单
            C~POSNN        "交货行项目
            D~BEZEI        "交货单类型
            E~BSTKD        "客户定单号
    INTO CORRESPONDING FIELDS OF TABLE I_SD
    FROM VBAK AS B INNER JOIN VBAP AS A
    ON B~VBELN = A~VBELN
    INNER JOIN VBFA AS C
    ON A~VBELN = C~VBELV AND A~POSNR = C~POSNV
    INNER JOIN TVAKT AS D
    ON B~AUART = D~AUART
    INNER JOIN VBKD AS E
    ON E~VBELN = A~VBELN
    FOR ALL ENTRIES IN I_DH
    WHERE "C~VBELN = I_DH-VBELN AND C~POSNN = I_DH-POSNR and
    ( C~VBTYP_N = ’J’ OR C~VBTYP_N = ’H’ OR C~VBTYP_N = ’T’ ).

后来我用ST05跟踪发现这个语句居然要用四分钟,其实一共也才20000条交货记录,后来到到SQL语句里面一看,原来这一句被系统分成了一万多条,而且每一条都执行成红色时间,晕倒之余,我用另一种方法让它不分解,先读出所有记录,然后再来删除

select
            A~VBELN        "销售订单号
            A~POSNR       "销售行项目
            A~KBMENG     "数量
            B~KNUMV       "条件码
            B~FAKSK        "冻结码
            B~AUART        "订单类型
            B~ERDAT        "订单日期
            A~NETPR        "净价
            A~WAERK  "工厂
            A~KPEIN          "销售单位
            C~VBELN AS SONUM        "交货单
            C~POSNN       "交货行项目
            D~BEZEI          "交货单类型
            E~BSTKD        "客户定单号
    INTO CORRESPONDING FIELDS OF TABLE I_SD
    FROM VBAK AS B INNER JOIN VBAP AS A
    ON B~VBELN = A~VBELN
    INNER JOIN VBFA AS C
    ON A~VBELN = C~VBELV AND A~POSNR = C~POSNV
    INNER JOIN TVAKT AS D
    ON B~AUART = D~AUART
    INNER JOIN VBKD AS E
    ON E~VBELN = A~VBELN
    WHERE C~VBTYP_N = ’J’ OR C~VBTYP_N = ’H’ OR C~VBTYP_N = ’T’ .
  LOOP AT I_SD.
    READ TABLE I_DH WITH KEY VBELN = I_SD-SONUM
                             POSNR = I_SD-POSNN.
    IF SY-SUBRC <> 0.
      _delete I_SD.
    ENDIF.
  ENDLOOP.

十秒!最后只用了十秒!再次晕倒!

总结:数据量小的时候可以用 FOR ALL ENTRIES IN ,因为系统里面的处理就像两个select语句循环,唉!我受苦了!分享给大家,希望大家不要再受苦了!

 
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Started by user admin Running as SYSTEM Building in workspace /var/jenkins_home/workspace/SGLD_SHIP_SYSTEM The recommended git tool is: NONE using credential 3daf7d0b-8918-4f92-9c5d-a3059a66a722 > git rev-parse --resolve-git-dir /var/jenkins_home/workspace/SGLD_SHIP_SYSTEM/.git # timeout=10 Fetching changes from the remote Git repository > git config remote.origin.url http://139.199.13.25:3000/shaofq/AG-SGLD-ILOG-1.0.git # timeout=10 Fetching upstream changes from http://139.199.13.25:3000/shaofq/AG-SGLD-ILOG-1.0.git > git --version # timeout=10 > git --version # 'git version 2.30.2' using GIT_ASKPASS to set credentials > git fetch --tags --force --progress -- http://139.199.13.25:3000/shaofq/AG-SGLD-ILOG-1.0.git +refs/heads/*:refs/remotes/origin/* # timeout=10 > git rev-parse refs/remotes/origin/master^{commit} # timeout=10 Checking out Revision 68b36471d8d68c7d5f6af47cd1c1025861e4cb03 (refs/remotes/origin/master) > git config core.sparsecheckout # timeout=10 > git checkout -f 68b36471d8d68c7d5f6af47cd1c1025861e4cb03 # timeout=10 Commit message: "feat:新增航线中文名维护" > git rev-list --no-walk 68b36471d8d68c7d5f6af47cd1c1025861e4cb03 # timeout=10 using settings config with name MySettings Replacing all maven server entries not found in credentials list is true [SGLD_SHIP_SYSTEM] $ /var/jenkins_home/tools/hudson.tasks.Maven_MavenInstallation/mymaven/bin/mvn -s /tmp/settings10948644921560376548.xml clean package -Pprod -Dmaven.test.skip=true -e Build step 'Invoke top-level Maven targets' marked build as failure Finished: FAILURE
最新发布
06-07
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值