Solution : https://service.sap.com/sap/support/notes/48230 (SAP Service marketplace login required)
Summary :
This SAP Note addresses performance issues related to the "SELECT ... FOR ALL ENTRIES ..." construct in ABAP's Open SQL, explaining the parameters to optimize its execution. The statement is a non-standard SQL enhancement allowing a join between internal ABAP tables and database tables. Since it lacks a direct SQL counterpart, ABAP's database interface adapts it into semantically equivalent SQL statements. Key profile parameters such as rsdb/prefer_join, rs have been outlined, with specific settings to either implement joins, use IN clauses with UNION or control how many table entries are processed simultaneously, impacting execution efficiency. Given the global effect of these parameters, SAP recommends a thorough analysis before implementing changes to avoid unintended system-wide issues.
Key words :
linked partial statements/conditions represents, symptom performance problems occur, semantically equivalent select statements, ] %_hints oracle '&prefer_in_itab_opt 1&&prefer_fix_blocking -1&', technical maximum blocking factor, heading database interface hints, terms for_all_entries reason, experienced sap consultants, -negated eq condition, ms sql server
Related Notes :
| 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 |