导致的数据缺失
for all entries in
自动去除重复数据的问题
以前也遇到过 但是没有引起足够的注意
而且发生错误时都是隐性的
很难找
所以开发程序 使用all entries in的时候就要考虑到这个问题
多选几个关键字段来避免被删除
原理的分析:
1)for all entries的解析
使用or 来连接多个条件
例如: for all entries in i_tab where a = itab-a and b = i_tab-b
itab中两条记录
a1 b1
a2 b2
那么解析的sql语句中使用or连接
where ( a = a1 and b = b1 ) or ( a = a2 and b = b2 )
2)使用的限制
另外In的解析也是使用or
过多的or造成了sql语句的复杂度
导致查询优化器无法选择最优方案
反而可能选了个最差的方案 由此造成性能问题
为了避免这个问题
连接多少个or是有限制的
默认是10个
这样例如500条记录的内表 ,实际生成50个sql语句,
即使如此,还是存在复杂度过大的问题
于是极端的方案是设置分拆的数量为1个
这样就等效到loop中select了!!
也并不是我们想要的
-------------------------------------------------------------------------------------------------
提供一种解决方案:
有限条件下可以使用
for all entries in <itab>导致多次查询
每次可能不慢 但是 <itab>的记录足够多
导致了瓶颈
如果业务允许 选取适合的条件
从目标table中抽取一次数据 for的目标结果是本次抽取的子集
是否需要过滤看实际需求 通常无须过滤
(提供一个过滤的思路 binary search + loop from to)
这样可以避免loop + loop的低效率
再增加一种解决方案
如果是单值的for all entries in
查询大数据量的表
在外部使用range变量存储查询条件
单次看似增加了db的部分压力 但是整体上是有显著提升的
是查询次数就压缩到了一次
-------------------------------------------------------------------------------------------------
for all entries in的效率并不是很高
单方便 而且比loop中select 靠谱
每次select的开销 以及缓存的数据可能被清空
以及for底层可能也有优化 所以这个效率的提高是可以保证的
但是和使用连接查询 子查询相比 效率还是不如
个人分析如下:
for使用的是分拆的方式去查询
每次查询的开销 竞争数据库连接 这是一个问题
另一个就是老外说的
不能找到最有执行计划 导致的cpu和i/o的消耗
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.
即使在有索引的过程中
按照老外的说法 也是有浪费的
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.
是否可这么理解 500条记录中真正需要的可能只有三五条 比如 需要找公司供应商信息之类的
但是分拆导致每次都要查询 级联/子查询先得到了数据范围 有个优化过滤了重复查询
这个是个人设想的极端坑爹环境:
在没有索引的情况下
全表扫描
对于大量数据的表不能每次完整的读入内存
需要分页 假设某张大表 要分成10页
cpu消耗差不多
本来只要IO十次 每页一次
但for 假设分拆成十次
IO会达到100次
===============================
使用for all entries
在查询的时候对应字段进行替换
内表中的字段部分属性会被丢弃
不能进行offset 等等之类的操作
for all entires 本质上还是多次拆分了查询
减少了几次查询的次数
但是并不能带来太大的效率提高
但是方便 逻辑处理 利于业务的展开及数据的处理
如果涉及效率问题并不见的有多好
子查询反而更有效果
同时这里这个效率问题
可见数据的传输量 读取量也是很重要的
所以避免无效字段 减少磁盘IO 内存消耗 网络带宽 都应该是优化考虑的地方
================================================================
FOR ALL ENTRIES的效率问题
2010-09-08 14:33:40| 分类： ABAP|字号 订阅
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)
omerb@srl.co.il
另外，附上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-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.
From <http://blog.163.com/wishuhappy@yeah/blog/static/13871426220108823340655/>
range变量存储查询条件
2015年1月9日
10:01
For all entries in
限制于sap的配置文件
拆分的基础数量是 5 or 10
10000条数据还是要几千次查询
单词查询效率不高的话 并不是很好
range变量缓存查询条件
但是sap能够编译的长度有限制 Range变量上限
手动来拆分
Exp.
DESCRIBE TABLE lt_dis_tmp LINES lw_lines.
LOOP AT lt_dis_tmp INTO ls_dis.
lw_index = lw_index + 1.
lrs_knumv-sign = 'I'.
lrs_knumv-option = 'EQ'.
lrs_knumv-low = ls_dis-knumv.
APPEND lrs_knumv TO lr_knumv.
IF lw_index MOD 999 = 0
OR lw_index = lw_lines.
SELECT knumv "单据条件
kposn "项目
stunr "步骤编号
zaehk "条件计数器
kschl "条件类型
kbetr "条件价格
APPENDING TABLE lt_konv
FROM konv
WHERE knumv IN lr_knumv "单据条件
AND kschl IN ('PB00', 'PBXX'). "条件类型
CLEAR: lr_knumv.
ENDIF.
ENDLOOP.
浙公网安备 33010602011771号