FOR ALL ENTRIES的效率问题

今天与jgtang82讨论FOR ALL ENTRIES与JOIN问题
SAP的数据字典对FOR ALL ENTRIES的封装也并不那么聪明。
关于效率问题，恰好有个不错的文章
记下来，以后可以备考
FOR ALL ENTRIES vs DB2 JOIN
http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

All abap programers and most of the dba's that support abap programmers are familiar with the abap clause "for all entries". Most of the web pages I visited recently, discuss 3 major drawbacks of the "for all entries" clause:

1. duplicate rows are automatically removed
2. if the itab used in the clause is empty , all the rows in the source table will be selected .
3. performance degradation when using the clause on big tables.

In this post I'd like to shed some light on the third issue. Specifically i'll discuss the use of the "for all entries" clause as a means to join tables in the abap code instead of in db2.

Say for example you have the following abap code:
Select * from mara
For all entries in itab
Where matnr = itab-matnr.

If the actual source of the material list (represented here by itab) is actually another database table, like:
select matnr from mseg
into corresponding fields of table itab
where ?

Then you could have used one sql statement that joins both tables.
Select t1.*
From mara t1, mseg t2
Where t1.matnr = t2.matnr
And T2?.

So what are the drawbacks of using the "for all entires" instead of a join ?

At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine uses (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste because it may lead to re-reading of data pages.
returing to the above example , lets say that our itab contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time.
Select * from mara
Where matnr in ( ...)

Db2 will be able to perform this sql statement cheaply all 50 times, using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will see that some of the data pages, especially the root and middle-tire index pages have been re-read each execution.

Even though db2 has mechanisms like buffer pools and sequential detection to try to minimize the i/o cost of such cases, those mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory. Had you coded the join, db2 would have known that you actually need 500 rows from mara, it would have been able to use other access methods, and potentially consume less getpages i/o and cpu.

In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.

In conclusion I suggest to "think twice" before using the "for all entries" clause and to evaluate the use of database views as a means to:
a. simplify sql
b. simplify abap code
c. get around open sql limitations.

Omer Brandis
DB2 DBA & SAP Basis professional (and all around nice guy)
[e-mail]

另外，附上NOTE 48230
Summary

Symptom
Performance problems with the open SQL statement "SELECT ... FOR ALL ENTRIES ...".

Other terms
FOR_ALL_ENTRIES

Reason and Prerequisites
The open SQL statement "SELECT ... FOR ALL ENTRIES ..." is an ABAP-specific enhancement of the SQL standard. This variant of the SELECT statement allows the ABAP programmer to join an internal program table with one or several database tables. (For a detailed description of that statement type please refer to the corresponding ABAP documentation.)
Since there is no analogous statement in the SQL standard, the open SQL statement has to be mapped from the database interface of the ABAP environment to one or several semantically equivalent SELECT statements which can be processed by the DB platform. Several profile parameters allow a definition of how the database interface should carry out this mapping with regard to the database. This note describes the parameters that can be used to control the "SELECT ... FOR ALL ENTRIES" statement and their effect.

Solution
The parameters mentioned in this note have considerable effects on most of the critical database commands and influence the performance of the whole system to a great extent. For this reason, before changing the parameters described in this note, a detailed problem analysis by experienced SAP consultants or the support team is required. Please note in particular that changing the parameters may often solve a local performance problem but it may also cause a still bigger problem to occur at another place. For this reason, prior to changing the profile parameters - which has a global effect on all statements - you should check first whether the performance problem might be caused by one or two positions in the corresponding application which can be corrected by a local change of the critical SQL statements.

The following profile parameters are available:

rsdb/prefer_join (ab Release 7.0)
           If you set this parameter to "1" the SELECT ... FOR ALL ENTRIES is implemented using a join. Note that this variant is only supported by the DB6 (DB2 UDB) and MS SQL Server database platforms.

rsdb/prefer_union_all
           You can override this parameter using rsdb/prefer_join = 1. The following remarks relate to rsdb/prefer_join = 0.


           Setting this parameter to "1" generates a linking of entire statements with UNION; setting it to "0" generates an OR link of conditions in the WHERE clause. Each of the linked partial statements/conditions represents an entry of the input table [itab].

For Example:
The open SQL statement

SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f.

is mapped to an SQL statement which is consistent with the standard:

rsdb/prefer_union_all            = 0
=>
SELECT ... WHERE f = itab[1]-f
          OR    f = itab[2]-f
          ...
           OR    f = itab[N]-f

rsdb/prefer_union_all            = 1
=>
          SELECT ... WHERE f = itab[1]-f
UNION ALL SELECT ... WHERE f = itab[2]-f
....
UNION ALL SELECT ... WHERE f = itab[N]-f

Where N is the number of rows in itab, and itab[i]-f is the value of
field f in the i-th table row.


rsdb/prefer_in_itab_opt
           If this parameter is set to "1", a statement where only one field in the WHERE clause depends on the converted internal table is reflected by a statement with an IN clause. However, this is only possible if the field reference and the WHERE condition are simple enough: in essential the field reference must be a not negated EQ condition.

For Example:
If parameter rsdb/prefer_in_itab_opt is set to "1", the open SQL

SELECT ... FOR ALL ENTRIES IN itab WHERE f = itab-f.

is mapped to the following SQL statement:

SELECT ... WHERE f IN (itab[1]-f, itab[2]-f, ..., itab[N]-f)


rsdb/max_blocking_factor
           This parameter specifies an upper limit for the number of entries taken in from [itab] to be processed in one statement. This means that if the internal table specified in the FOR ALL ENTRIES clause contains more than rsdb/max_blocking_factor rows, the open SQL statement is split into several statements for the database the results of which are collected in the DB interface and then returned as an overall result to the ABAP program. For an internal table with N rows

N / "rsdb/max_blocking_factor" + 1

individual SELECT statements are issued for the database. However, this parameter has no effect on the translation to IN (...) (for prefer_in_itab_opt).

           Additionally the technical maximum blocking factor is calculated for each statement, so no limits of the database system are exceeded. If the limit of the blocking factor is lower than max_blocking_factor, the limit is used implicitely.

rsdb/max_in_blocking_factor
           This parameter, analogously to rsdb/max_blocking_factor, gives the upper limit for the number of entries to be processed from [itab] if the concrete statement is reflected on an IN clause (see prefer_in_itab_opt).

           Analogously to rsdb/max_blocking_factor also the limit of the blocking factor is used instead of rsdb/max_in_blocking_factor, if otherwise the upper limits of the database system would be exceeded.

rsdb/prefer_fix_blocking
           If the number of entries in [itab] is not divisible by max_blocking_factor, less entries (conditions) are allocated to the last statement which has been generated for processing the FOR ALL ENTRIES statement. The result is a new statement.
If the same FOR ALL ENTRIES statement is executed very frequently with a different number of entries in the input table [itab], different statements are created up to the maximum of max_blocking_factor statements.
This can be avoided by the above profile parameter.
If this parameter is set to "1", at most two statements of different length are generated. This is achieved by repeating the last value in the input table as if [itab] has been padded to the blocking factor ([itab] is not really modified).

rsdb/min_blocking_factor
           If this parameter is set to a value larger than "0" AND if rsdb/prefer_fix_blocking is set, 2 different blocking factors are used: a smaller (min_blocking_factor) and a larger factor (max_blocking_factor).
However, the min_blocking_factor is only used if there are only a few entries in [itab]: A little simplified, if the following applies: "Entries [itab] < max_blocking_factor / 2"

rsdb/min_in_blocking_factor
           This parameter works in conjunction with rsdb/min_blocking_factor, for the case that the addition FOR ALL ENTRIES has been implemented with an IN clause (see prefer_in_itab_opt).


Control over FOR ALL ENTRIES Hints
Under the heading Database Interface Hints, Note 129385 describes the options you have for influencing the database interface by entering hints. The hints are evaluated in the database interface itself and are not passed on to the database.

Starting with kernel Release 4.6B all the above mentioned FOR ALL ENTRIES parameters can be set via such a hint for a single statement. In the example:
  SELECT * FROM [..] FOR ALL ENTRIES IN [..] WHERE [..]
  %_HINTS ORACLE '&prefer_in_itab_opt 1&&prefer_fix_blocking -1&'.
This way, the boolean parameter 'prefer_in_itab_opt' is explictly set and the boolean parameter 'prefer_fix_blocking' is set to its default value.

FOR ALL ENTRIES hints, like hints are generally only used as a a corrective device in emergency situations; Note 129385 goes into this. The hints described here should only be used with careful consideration.

评论

12 楼 王三 2009-02-10  

文章很有价值,谢谢

11 楼 blueoxygen 2007-09-13  

呵呵，是呀。说的严格一些比较好。不过，平时有时间看文档的时候，看BC4X系列的时间极少，他们躺在我硬盘里好久了，基本都在看模块方面的了。有时间你可以多多介绍一些技术细节。

10 楼 jgtang82 2007-09-13  

聊聊而已,哪有什么成本? 时间成本? 正无聊着呢:)

9 楼 blueoxygen 2007-09-13  

我说的enhancement不是SAP培训里的enhancement的glossary，而是程序类型，可能不同公司叫法不同。
不管修改系统程序，实现badi还是做function，这种对系统标准流程的补充，我们都叫enhancement.
有些时候buzz word搞得太多会增加交流成本。

8 楼 jgtang82 2007-09-13  

1.确需要access key来做的应该叫Modification, 不需要的叫Enhancement.
2.User Exit 属于modification, 它需要access key, 以va02为例,在它对应的程序中search "userexit" in main program, 你会发现user exit是在system namespace中的.
Customer exit和BAdI属于enhancement, 不需要access key.这也是user exit和customer exit的一个主要差别.另它们的找法和实现过程也不是一样的,详见BC425.
3. BAdI是可以自己创建的,然后放在customer程序里面让别人在上面再做二次开发.

呵呵,搞得有点像在扣字眼一样,但准确的概念有时也是必要的,不对的地方请指正. SAP的圈子太小,大家这些东西讨论的也少.

7 楼 blueoxygen 2007-09-13  

不错，不错。
我想让SAP拥有的user-exit是啥呢？是像spring一样，可以用户随时将扩展配置进去的。
比如现在很多enhancement不申请key来更改系统标准程序还真是搞不定的。这样总是心惊胆颤的，升级以后也什么都没了。
如果可以将enchacement内容单独写在class或者function里，然后通过某个TCODE随意配置入系统的某个标准程序，那就放心多了。

6 楼 jgtang82 2007-09-13  

说有AOP的影子是指一个BAdI可以允许它有多个Impl，在调用的时候还可以通过Filter来选择哪些会被执行。这里在用Adapter class 调用Impl instance的方式有Observer pattern的影子。另外我只是说有AOP的影子，要不然就是放大BAdI的功能了。
如何找Customer Exit, User Exit, BAdI是应用方面的事了，找法也算很简单，SDN上到处都有对吧:)

5 楼 jgtang82 2007-09-13  

customer exit是通过调用包含customer namespace文件的function来实现的，没什么好说的。不过BAdI做的还是不错的，背后有AOP的影子，其实现机制应用了simplified abstract factory(EIXT_HANDLER), proxy pattern(BAdI interface, auto generated Adapter class(这个名字叫的不合适，从现在DP的定义上来说叫Porxy我觉得更合适), customer impl), 另外EXIT_HANDLER=>GET_INSTANCE里面还有元数据编程(reflection)的一些内容，以前觉得ABAP是古董，其实里面是真有不少东西的。当然在现在看来高明说不上，但在十年前应该算是还不错的。

4 楼 blueoxygen 2007-09-12  

我目前做项目，除了常识性的编程错误引起的性能问题(比如LOOP中SELECT)，一些棘手的性能调优都是BASIS顾问给建议。所以这些细节追的少了。
function实现的user-exit和BADI的，我都感觉设计没啥可高明的，以前大学跟实验室做产品都会类似这样做扩展接口，但是SAP对哪些地方留了扩展的接口却很有研究的意义。这些可都是经过业务上的精心筛选的。
能像JAVA一样做拦截器，如此来扩展，我感觉能优雅一些，可惜已超出ABAP的能力。

3 楼 jgtang82 2007-09-12  

这些技术细节有时还是很有价值的，如果没有这些细节知识Performance Tuning没法做呀。这方面BC490那篇标准文档讲的不错，不过原理些的东西讲的不够清楚。那个Notes我是第一次看到，不知道Profile parameters对For all entries in的影响，长知识了。我觉得SAP方面国内很少有人去分析一些技术的幕后机制，比如说BAdI的设计机制，应用了哪些设计模式等等。可能主要还是做的人少了。

2 楼 blueoxygen 2007-09-12  

多亏了你的提醒啊。否则我也不会把这条NOTES查出来。
不过做SAP的对这种技术细节追究的人少，而且也不是像基础软件开发那么重要。大部分时间，help里查得还是业务知识呀:P

1 楼 jgtang82 2007-09-12  

呵呵，blueoxygen真是个执著的人，学习:)