ABAP程序效率优化系列之②——开发优化之读取数据库

ABAP程序效率优化系列文章历史

跟上一期一样,我在下面列出ABAP中OPEN SQL的代码建议。

(非常重要的用红色加粗表示)

重复项-DISTINCT

尽量避免使用SELECT DISTINCT语句,使用ABAP的SORT + DELETE ADJACENT DUPLICATES 代替

排序-ORDER BY

使用ABAP的SORT代替SQL中的ORDER BY,可以降低数据库服务器的负载。

写数据库-INSERT

使用INSERT dbtab FROM TABLE itab,而不是在LOOP中INSERT INTO dbtab VALUES wa.

读取数据到内表

1、尽量不使用SELECT *,而是列出索取字段

2、一般写法为SELECT FIELDS INTO TABLE itab FROM dbtab。如果后续有数据处理,还会用到LOOP AT itab的处理。但是如果我们只处理数据一次,比如取出bseg的金额,根据shkzg对金额进行简单的正负运算后存到内表中(高版本在OPEN SQL中提供了处理的语法,请忽略),那么建议使用SELECT. APPEND. ENDSELECT的循环处理方式。这样做的效率更高。

嵌套查询、子查询

嵌套查询:SELECT 1 / SELECT 2 / ENDSELECT / ENDSELECT.

子查询:类似于SELECT WHERE EXISTS ( SELECT WHERE )的方式

子查询优于嵌套查询也优于SELECT ... INTO TABLE ITAB / SELECT ... FOR ALL ENTRIES IN ITAB的二次查询方式

子查询写法示例:

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

ENDSELECT.

(代码取自SE30提示与技巧)

RANGE和FOR ALL ENTRIES IN

1、FOR ALL ENTRIES IN itab使用前,要判断itab是否为空

2、一般情况下(法无定法,具体情况具体分析),数据量小(小于3000),用for all更快。数据量大(大于3000)时,用range更快,即便是分多次处理也是range更快

(range条目过多时,OPEN SQL可能会dump,需要分多次处理)

查询条件的选择

尽量使用索引字段,包括KEY字段、系统提供的索引字段、自己创建的索引字段

使用游标

使用游标的好处是:

1、避免重复打开SQL语句,避免重复让数据库为SQL语句生成执行计划,节省时间

2、避免一次取出大量的数据,避免程序因内存过大而崩溃

3、降低内表数据量也有助于提升内表循环/嵌套循环的处理速度

使用方法请参见OPEN CURSOR的F1帮助,或从网上查询使用示例。

索引字段

1、根据需要创建合适的索引字段,索引字段过多对系统也是累赘

2、指定索引字段时,按从左到右的顺序指定。比如表SFLIGHT,包含主键CARRID、CONNID、FLDATE。

按从左到右的顺序指定,不是说在where条件里要把CARRID写在CONNID和FLDATE的前面,where carrid = '' and connid = '' and fldate = ''和where connid = '' and carrid = '' and fldate = ''没有区别。

从左到右的顺序,指的是对于索引中位于最前面的字段,优先限定选择条件。

比如,根据CARRID=AA和CONNID=0017去检索,系统会根据索引检查AA0017%的数据。如果根据FLDATE=20051201检索,系统会根据索引检查______20051201的数据。

后者比前者更慢。

所以,采用索引字段时,位于左侧的字段尽量不要留空或使用占位符_或使用匹配符%。

(绿色部分的说明,取自于PA教材)

运算符

【来自PA】

1、对于索引字段,尽量避免使用NOT、NE、,尽量使用肯定表示法

a) 逻辑非(NOT运算符)通常用于在检索合适的索引时,防止优化器选择相关的字段。如果因此无法找到合适的检索范围,则确定相应命中列表的处理量可能变得非常高,从而导致运行时间变长。

b) 逻辑非所涉及的不包含在索引中的字段则不会造成该问题。它们仅用于减少命中数量。

c) 如果无法避免对索引字段使用逻辑非(例如,因为所需 IN 列表会变得过大),则您应该指定逻辑非,以减少必须传输的数据量。

3、运算符的速度排序

速度:= > IN > BETWEEN > LIKE > NOT

【但有时SQL语句执行时,系统自动分配的先后顺序可能会导致取数更慢。

如:根据BWART和BUDAT_MKPF(建立索引)取MSEG,如果BWART用IN,BUDAT_MKPF用BT,系统可能会先根据BWART取数。这时,尽量避免对BWART使用IN,应改为BT,让BWART和BUDAT_MKPF处在同一层级的运算符上,让数据库方便优化SQL的执行(也可以使用下一条里介绍的%_HINTS)】

SQL语句的人工干预(以ORACLE为例)

1、指定SQL语句使用表的某个索引

如:

DATA:lt_mseg TYPE TABLE OF mseg.

SELECT * INTO TABLE lt_mseg FROM mseg

WHERE bwart = '261'

AND budat_mkpf BETWEEN '20170701' AND '20170801'

%_HINTS ORACLE'INDEX("MSEG" "MSEG~Z05")'.

(此例中,Z05是我对MSEG自己创建的索引,索引字段是budat_mkpf)

如果我不指定索引,系统可能会优先根据bwart去access表mseg(因为=的速度大于BT的速度),然后再根据budat_mkpf进行filter,而261移动类型的数据是相当多的,这样的SQL相当慢,需要我们指定索引人工干预SQL的执行。

【注:

a) 我这里说可能会,不是说一定会。ORACLE生成SQL的执行计划时,会根据数据量的大小和以往SQL语句执行的统计信息,自动优化代码,但这种自动优化的结果并不一定是最优的,很多时候都需要我们的人工干预。

b) 上面提到了access和filter,这些我们在下一期(ST05的那些事儿)里重点分享。】

2、对于For all entries in itab的优化

首先理解for all entries in的机制。

系统中有一个参数(可以用TCODE:RZ11查看)叫做rsdb/prefer_in_itab_opt,当where条件只用到itab的一个字段时:

a) 如果prefer_in_itab_opt为0,则for all语句执行时,对于itab中每条数据都转化为or的方式,即field = itab[1]-value or field = itab[2]-value or field = itab[3]-value

b)如果prefer_in_itab_opt为1,则for all语句执行时,itab中每条数据都会转化为in的方式,即field in (itab[1]-value, itab[2]-value, itab[3]-value)。而in的速度比or快。

系统中还有一个参数叫做rsdb/max_in_blocking_factor。当itab中有1万条数据时,可能会被拆解成多个sql执行,而max_in_blocking_factor控制着in运算符能包含的最大的条目数(还有min_in_blocking_factor控制最小的条目数,不常用)。

第二个参数不适用于簇表!!!

(这两个参数,可以通过RZ11设置,也可以用%_HINTS指定,以后者的优先级为最高,且使用后者指定时,不需要写rsdb/)

用法示例:

SELECT * FROM MSEG

FOR ALL ENTRIES IN itab

WHERE mblnr = itab-mblnr

%_HINTS ORACLE '&prefer_in_itab_opt 1& &max_in_blocking_factor 500&'

它指定了当只使用for all entries in itab中的一个字段时,自动转为in的模式,并且每个SQL语句的IN运算符最多可以包括500条数据。如果itab有2300行,那么这个SQL语句在后台会实际执行5次。

尽量不在循环中使用SELECT SINGLE,而使用FOR ALL ENTRIES IN

依然法无定法,只是说尽量。而且有时使用select single还更快。

比如取BSEG的数据(不是簇表的版本请忽略),当确定BSEG的所有主键时,select into table from bseg for all entries in要比loop. select single. append. endloop.的方式花的时间多很多。而当不确定BSEG的所有主键时,FOR ALL执行的时间也比loop. select appending. endloop.的方式要多一些(差不多太多,但还是for all慢)。

不清楚这是不是簇表的原因,有知道的大顾们,欢迎留言告诉我原因。

测试代码(老白的ABAP群里的大S黄顾问的测试代码)放在百度网盘上了。

链接:https://pan.baidu.com/s/1jJ0VZc6

密码: 3ajk

但对于在稍大一点的循环(比如1000条以上)中取公司代码描述、工厂描述、科目描述、人员姓名一类的代码,我们是坚决不能忍的!

其它未尽的手段

除上述纯ABAP手段外,还有数据归档、优化表空间、碎片整理、索引重组、硬件扩容等,这些需要业务顾问、BASIS顾问的介入甚至主导。我不是BASIS顾问,就不在此妄言了。但请记住,这些是整个服务器的性能瓶颈凸显后,极为极为重要的优化手段。

本期结束。

请期待下一期,ST05的那些事儿。

IOS/ANDROID用户打赏——赞赏码

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180201G04UZA00?refer=cp_1026

相关快讯

扫码关注云+社区