财务报表性能分析--实例

时间:2023-01-09 07:37:21

1. select distinct kaufn as vbeln bukrs
      into corresponding fields of table it_so1
      from ce11000
      where kaufn in s_vbeln
        and perio in s_perid
        and vrgar in ('A','B','F')
        and paledger = '02'
        and plikz = '0'
        and bukrs in s_bukrs.

 

 

 2.   select * into corresponding fields of table it_ce11000_tmp
        from ce11000
        where kaufn = it_so-vbeln
          and vrgar in ('A','F','B')
          and paledger = '02'
          and plikz = '0'
          and bukrs = it_so-bukrs.


在QAS500下测试,ce11000表中有两千多万的数据量,输入SO筛选条件(如:6P*),1的速度几秒能通过,而2的速度需10分钟左右

 

注:kaufn vrgar is index , paledger is key


  3.   select kaufn into corresponding fields of table it_ce11000_tmp
        from ce11000
        where kaufn = it_so-vbeln
          and vrgar = 'A'
          and paledger = '02'
          and plikz = '0'
          and bukrs = it_so-bukrs.

   4.  select kaufn into corresponding fields of table it_ce11000_tmp
        from ce11000
        where kaufn = it_so-vbeln
          and vrgar in ('A','B','F')
          and paledger = '02'
          and plikz = '0'
          and bukrs = it_so-bukrs.
第一次读取时,3 一两秒,4 则要1分钟 第二次循环时,则速度一样(为什么这样?应该是buffer起作用)(不知道这样理解对不对?)
另外,如果在屏幕中输入SO : 6P* ,则两者速度一样。。。。


   
  5.  在loop 循环外一次读取所有数据

 select * into corresponding fields of table it_ce11000_tmp
        from ce11000
        for all entries in it_so
        where kaufn = it_so-vbeln
          and vrgar in ('A','B','F')
          and paledger = '02'
          and plikz = '0'
          and bukrs = it_so-bukrs.

     sort it_ce11000_tmp by kaufn.
     loop at it_ce11000_tmp.
       move it_ce11000_tmp to it_ce11000.
       collect it_ce11000.
     endloop.
     clear: it_ce11000_tmp,it_ce11000_tmp[].

注: 如输入SO筛选条件,此语句几秒能读取, 若没有输入SO 条件,则需要2分钟,
     但在SO数据量大的情况下,若800条,则次语句需要10分钟左右。

 

6. select distinct kaufn as vbeln bukrs
      into corresponding fields of table it_so1
      from ce11000
      where kaufn in s_vbeln
        and perio in s_perid
        and vrgar in ('A','B','F')
        and paledger = '02'
        and plikz = '0'
        and bukrs in s_bukrs.

此段代码在没有输入SO的情况下,很慢10分左右。。。kaufn 放前面做筛选条件,可能导致性能下降(不知是否受影响)。。。


  select  kaufn as vbeln bukrs
      into corresponding fields of table it_so1
      from ce11000
      where perio in s_perid
*        and bukrs in s_bukrs
        and paledger = '02'
        and vrgar in ('A','B','F')
        and plikz = '0'
        and kaufn in s_vbeln.
  sort it_so1 by vbeln.
  delete adjacent duplicates from it_so1.
  loop at it_so1 where bukrs not in s_bukrs.
    delete it_so1.
  endloop.

次语句需要10分钟,在sql 语句中bukrs条件没有提高多大性能在此, 在ce11000表中直接读数据,若输入了bukrs 条件,需10多分钟
不输入bukrs ,则一分钟内可读取完(不知道为什么?)


7. 我也不知道为什么要从ce11000中找SO?


find the SO that no shipment at that period,
 but have manaul posting

 

注:其中vrgar = 'B' 一个月的数据量大时过百万,

     select   kaufn plikz vrgar perio  kstar ww021 vv801 vv802 vv803 vv804 vv805  vv806
        vv807 vv808  vv809   vv810   vv811 vv812 vv813 vv814 vv815  vv816 vv817 vv818  vv823
        vv873 vv879  vv882   vv883  vv819  vv888  vv884  vv887  vv864 vv872  vv885  vv886  vv840 vv837
        vv880 vv881
       into table it_ce11000_tmp
        from ce11000
        where kaufn = it_so-vbeln
          and vrgar eq 'A'
          and paledger = '02'
          and plikz = '0'
          and bukrs = it_so-bukrs.

A数据量过百万,此语句需7、8分钟,若放在loop 循环下。。。性能下降可想而知

 

8、the enhance method:


     loop at it_so.
      s_so-sign = 'I'.
      s_so-option = 'EQ'.
      s_so-low = it_so-vbeln.
      APPEND s_so.
     endloop.
     sort it_so by vbeln.
     delete adjacent duplicates from it_so.
     select kaufn plikz vrgar perio  kstar ww021 vv801 vv802 vv803 vv804 vv805 vv806 vv807 vv808  vv809   vv810   vv811 vv812 vv813 vv814 vv815  vv816 vv817 vv818 vv823 vv873 vv879  vv882   vv883  vv819  vv888  vv884  vv887  vv864 vv872  vv885  vv886  vv840 vv837   vv880 vv881
         into  table it_ce11000_tmp
        from ce11000
*        for all entries in it_so
*        where kaufn = it_so-vbeln
        where kaufn in s_so
          and paledger = '02'
          and vrgar in ('A','B','F')
          and plikz = '0'
          and bukrs = it_so-bukrs.

 

此语句7分钟,it_ce11000_tmp 有1万多条数据,s_so有700条数据

 

 

   clear s_so.

     sort it_ce11000_tmp by kaufn.
     loop at it_ce11000_tmp.
       move it_ce11000_tmp to it_ce11000.
       collect it_ce11000.
     endloop.
     clear: it_ce11000_tmp,it_ce11000_tmp[].

 

 

 

 

 

总结:

 

    a、在大数据量时尽量利用索引关键字查询,注意where条件后面的顺序

    b、避免在loop循环中执行select 等耗时的查询动作,应在loop外一次读取所有数据

    c、注意for entries in 的用法,此实例中若用for entries in 性能好像差不多

        (具体它的机制,暂时还不太明白,所以没用这种写法, 另外在非唯一字段下会删除该字段的重复记     录)

    d、避免在耗时的语句中用select * ,在大数据量的情况下,查询少数字段比查询全部能

        提高很大性能,corresponding fields of table 能不用最好,也能提升性能,

        具体能提升多少,未知。。