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用户打赏——赞赏码