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 能不用最好,也能提升性能,
具体能提升多少,未知。。