1/8/2016 7:38:41 AM

I looked at various performance tuning techniques and tried to identify some that are more important than others. Both join and FAE can be used but depends on the situation

Although it's an old topic, but I would like to take a chance comment on Join vs For All Entries

Join:

1. Join is an generic SQL statements

2. Many tables can be joined

3. It will hit Database at one time.

For All Entries:

1. ForAll Entries is an ABAP Query

2. We can use multiple FAE in single select

3. It will hit Database based on packet size(particularly rsdb/max_blocking_factor, rsdb/max_in_blocking_factor) so obviously the call will be high between Application Server to Database Server

Note that RSPARAM always displays a value of -1 for these parameters when you use the default values. You can determine the value actually in use by referring to the dev_w* work process trace (transaction ST11).

If the FOR ALL ENTRIES list is empty, all data is generally selected from the current client ("SELECT ... FROM <table> WHERE MANDT = :A0"). All conditions in the WHERE part are ignored.

If tables in R/3 are completely or generically buffered, the buffers are reloaded, if necessary, with special DBI statements (for example, "SELECT * FROM <table> WHERE MANDT = :A0 ORDER BY <primary_key_fields>" for completely buffered tables) that may be completely different to the statement from the ABAP source code.

The DBI provides ABAP statements that have "SELECT SINGLE" or "UP TO ROWS" without ORDER BY with the FIRST_ROWS hint and "WHERE ROWNUM <= ..." 

Some operations (for example, kernel procedures, bulk operations, generations) can generate SQL statements although there is no regular SQL statement in the ABAP source code.

Even when you use conversion exits, there can be significant differences between ABAP source code and database-side statements (such as additional conditions that are not explicitly present in the ABAP source code).

IN conditions from the ABAP source code may be converted into any number of possible conditions on the database level, depending on your selection criteria: "=", "IN", "LIKE", "BETWEEN", ">", "<", ">=", "<="

Columns that appear in both the selection list and the WHERE condition are removed from the selection list if it is clear from the WHERE condition what the column's value must be.

If an expression ends with a space followed by a placeholder, the system generates an OR connection as follows:

           SQL statement: ... WHERE <column> LIKE '<string> %'

           Statement after DBI transformation: ... WHERE (<column> LIKE '<string> %' OR <column> LIKE '<string>')

Both results are same but only the concern is execution time. I have worked various performance tuning and I have used For all entries sometimes.

Suppose we have more than 20K records in Range table and when I pass in to where clause using 'IN' operator system will give dump which is standard behavior.  In this case I have used FAE so system will not give dump but the Database call will be more as I said earlier.

Suppose in your range table having duplicate entries then system will not delete the duplicate entries instead select all the entries and filter the records. Whereas using FAE system will internally delete the duplicate entries and passing to Database to fetch records.

INNER JOINs only look at the intersection of the results that meet the WHERE clause.

FOR ALL ENTRIES eliminates duplicates from the results.

When using FOR ALL ENTRIES you generally end up with at least two internal tables. This may or may not be a good thing.

If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.


COMMENTS

  • Bala -7/21/2016 3:04:56 PM

    Nice Article