SAP Note 48230 - Parameterization Parameterization for SELECT SELECT ... FOR ALL ENTRIES statement Note Language: Language: English
Version: 17 Validity:
Valid Since 30.05.2012
Summary Symptom Performance problems occur 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, refer to the relevant ABAP documentation.) Since there is no similar 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 that 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. 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 in 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 that can be corrected by a local change of the critical SQL statements. The following profile parameters are available: o
rsdb/prefer_join If you set this parameter to "1", the SELECT ... FOR ALL ENTRIES is implemented implemented using a join. As of Release 7.00, the parameter is supported only for the database platforms DB6 (DB2 UDB) and "MS SQL Server", and it is supported for Oracle as of Release 7.10.
o
rsdb sdb/pre /prefe fer_ r_un unio ion_ n_al all l 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 01.11.2012
Pa g e 1 o f
5
SAP Note 48230 - Parameterization for SELECT ... FOR ALL ENTRIES statement 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 independent of the parameter rsdb/prefer_union_all to an SQL statement, which is consistent with the standard: rsdb/prefer_union_all => SELECT ... WHERE f = itab[1]-f OR f = itab[2]-f ... OR f = itab[N]-f
= 0
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.
o
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 possible only if the field reference and the WHERE condition are simple enough: essentially, the field reference must be a non-negated EQ condition. For example: If parameter rsdb/prefer_in_itab_opt is set to "1", the open SQL statement 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)
o
rsdb/max_blocking_factor This parameter specifies an upper limit for taken in from [itab] to be processed in one that if the internal table specified in the contains more than rsdb/max_blocking_factor
01.11.2012
the number of entries statement. This means FOR ALL ENTRIES clause rows, the open SQL Page 2 of
5
SAP Note 48230 - Parameterization for SELECT ... FOR ALL ENTRIES statement 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 mapping to IN (...) (for prefer_in_itab_opt). Additionally, the technical maximum blocking factor is calculated for each statement, so that 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. o
rsdb/max_in_blocking_factor This parameter, similar to rsdb/max_blocking_factor, specifies the upper limit for the number of entries to be processed from [itab] if the concrete statement is mapped to an IN clause (see prefer_in_itab_opt). Analogously to rsdb/max_blocking_factor, the limit of the blocking factor is also used instead of rsdb/max_in_blocking_factor, if otherwise the upper limits of the database system would be exceeded.
o
rsdb/max_union_blocking_factor If the mapping of a "FOR ALL ENTRIES" clause to a statement occurs with UNION, the system uses a blocking according to rsdb/max_blocking_factor. However, if a different blocking number is more beneficial in the case of UNION, this can be specified using the parameter rsdb/max_union_blocking_factor. As a result, this number overrides the value of rsdb/max_blocking_factor for the UNION mapping. This parameter was introduced with kernel Release 720.
o
rsdb/prefer_fix_blocking If the number of entries in [itab] cannot be divided by max_blocking_factor, less entries (conditions) are allocated to the last statement that 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 implicitly filled to the blocking factor ([itab] is not really modified).
o
rsdb/min_blocking_factor If this parameter is set to a value larger than "0" AND if
01.11.2012
Page 3 of
5
SAP Note 48230 - Parameterization for SELECT ... FOR ALL ENTRIES statement 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" o
rsdb/min_in_blocking_factor This parameter works in conjunction with rsdb/min_blocking_factor, in the case that the addition FOR ALL ENTRIES has been implemented with an IN clause (see prefer_in_itab_opt).
o
rsdb/min_union_blocking_factor This parameter works in the same way as rsdb/min_blocking_factor for the situation that the FOR ALL ENTRIES addition is displayed in a UNION. This parameter was introduced with kernel Release 720.
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 using 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 explicitly set and the Boolean parameter 'prefer_fix_blocking' is set to its default value. The parameter rsdb/max_union_blocking_factor that was introduced for Release 720 cannot be specified in a hint. In this case, you can use the existing hint max_blocking_factor even if the statement is displayed in a UNION. "FOR ALL ENTRIES" hints, like hints, are generally used only as a a corrective device in emergency situations; refer to Note 129385. The hints described here should only be used with careful consideration.
Header Data Release Status: Released on: Master Language: Priority: Category: Primary Component:
Released for Customer 30.05.2012 13:59:43 German Recommendations/additional info Performance BC-DB-DBI DB Independent Database Interface
Secondary Components: MM-IV Invoice Verification
01.11.2012
Page 4 of
5
SAP Note 48230 - Parameterization for SELECT ... FOR ALL ENTRIES statement
The Note is release-independent
Related Notes Number
Short Text
1719450
MaxDB: Parameter setting for 'Select for all Entries'
1622681
DBSL hints for SAP HANA
1500503
BI quantity conversion: Access optimization
1456251
DB6: Complex SQL statements due to many UNION ALL with FAE
1417305
Dynamic changing of FAE parameters
1091419
DB6: Poor Performance for queries with FOR ALL ENTRIES
881083
Blocking factors on Oracle-based systems
869006
Composite SAP note: ORA-04031
819324
FAQ: SAP MaxDB SQL optimization
766349
FAQ: Oracle SQL optimization
652634
FOR ALL ENTRIES performance with Microsoft SQL Server
634263
Selects with FOR ALL ENTRIES
620361
Data loading performance/Admin. data target, many requests
70964
DB2/390: Performance
01.11.2012
Page 5 of
5
