Skip to Content
18

For All Entries is NOT better than INNER JOIN in most cases

I quote from Siegfried Boes' excellent post here: Will writing an inner join be better or creating a view?

For all the FOR ALL ENTRIES lovers ... there is no proof for these reappearing recommendation.
There is nearly nobody who receives forum points, who recommends FOR ALL ENTRIES instead of Joins. What is the reason ???

It is easier to prove the opposite. A Join is a nested loop inside the database, a FOR ALL ENTRIES is partly outside of the database. FOR ALL ENTRIES works in blocks, joins on totals.

FOR ALL ENTRIES are not recommded on really large tables, because the chances are too high that
too many records are transferred.

People prefer FOR ALL ENTRIES, because JOINs are not so easy to understand. Joins can go wrong, but with a bit of understanding they can be fixed.

Some Joins are slow and can not be fixed, but then the FOR ALL ENTRIES would be extremely slow.

There are several kinds of views:

- projection views, i.e. only one table involved just fields reduced
- join views, several tables, joins conditions stored in dictionary 
- materialized views, here the joined data are actually stored in the database. Storing and synchronisation has to be done manually.

Only the last one creates real overhead. It should be the exception. 

Join Views and Joins are nearly identical. The view is better for reuse. The join is better in complicated, becuase if the access goes wrong, it can often be fixed by adding a hint. Hints can not be added to views.

Abraham Bukit points out:

If it is cluster table, (you can't use join). If it is buffered table, I would also say avoid join.
If they all are transaction table which are not buffered and are not cluster tables.

He further supports Siegfried's statement that FAE is easier to undestand than INNER JOINs.

Thomas Zloch says, regarding buffered tables:

At least think twice, maybe compare runtimes if in doubt.

So, unless someone has some EVIDENCE that FOR ALL ENTRIES is better, I don't think we want to see this discussed further.

Kind regards

Matt

Add a comment
10|10000 characters needed characters exceeded

Related questions

16 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 28, 2010 at 01:32 PM

    Using FOR ALL ENTRIES is one way to approximate an SQL join or a database view. The join

    and the view will typically yield significantly better performance than FOR ALL ENTRIES.

    taw12 , part 1, page 255

    Edited by: bilen cekic on Sep 28, 2010 3:33 PM

    Edited by: kishan P on Nov 2, 2010 2:26 PM Format Fixed

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 14, 2011 at 11:36 AM

    Hi,

    just a comment, since most ansers I have seen dont consider the following, but I have just screened the

    remarks regarding performance only briefly:

    A "For All Entry" Statement will lead to a longer runtime and you will block a batch process on

    an application server for a longer time period, if you compare this with an "Inner Join".

    This is quite obvious, afterall if you are doing a "For All Entry", you are doing the Join in ABAP.

    So what does this mean: You are reducing the loading peaks on the database level transfering

    the work to do, to the network and to the application server.

    For All Entry will also allow you to partion the calls, simply if you split up your internal table in

    sub tables - if you do this, you trade the time the job is running for less volatility in the peaks you have

    on the network / the database.

    Also - if the hardware resources on an Application Server is too low, you easily add an add. Applicaton

    Server in a fairly short time, try to find a similar solution, if database resources are on the limit.

    And thats it.

    I have experience with large data volumens, multiple millions of records on a daily basis, and I like / prefer

    "For All Entry", since it allowes me to control the ground load in the system.

    I dont really care, if the job needs 1 or 2 hours more, as long as I stay in the time window I neeed to meet.

    But that is for my scenario, if you dont have the luxury to say "I dont really care, if the job needs 1 or 2 hours

    more", but you have the luxury to say - all peoble leave the system, until the jobs are done, than go for

    "Inner Join".

    With kind regards

    Uwe Gebhardt

    PS: I have experience with large data volumens, multiple millions of records on a daily basis, and I like

    "For All Entry", since it allowes me to control the ground load in the system.

    Edited by: Uwe Gebhardt on Apr 14, 2011 1:36 PM

    Edited by: Uwe Gebhardt on Apr 14, 2011 1:37 PM

    Edited by: Uwe Gebhardt on Apr 14, 2011 1:38 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Although it's an old topic, but I would like to take a chance comment on Uwe's post from 14th of April.

      Let's see if Uwe's suggestion (For all entries) really reduces database stress.

      Assuming the JOIN is used to deliver an intersection. Table A and Table B separately would retrieve more entries that the result of the JOIN.

      Task: join tables A and B, table A is accessed with 2 fields in WHERE and table B also with 2 fields.

      Approach 1: make a DB join.

      Actions on Database:

      Assuming that DB is starting with the table which has more selective fields in WHERE clause (nested loop, often the case).

      1. Index access for table A ( N blocks are read) with 2 fields of table A, both in index

      2. Table access for table A to extract fields necessary for the join and fields that are selected. (M blocks are read)

      3. Index access (primary key) to locate needed table entries of table B (O blocks read).

      4. Table access for table B and filtering the result using 2 fields of table B. (P blocks read)

      5. Passing back X entries (keep in mind network capacity between application server and database server).

      In total N + M + O + P blocks are read, plus very few CPU time spent on filtering of the result, plus some time to pass back X entries.

      Approach 2: split selects using For All Entries.

      Actions on Database:

      1. Index access for table A ( N blocks are read) with 2 fields of table A, both in index.

      2. Table access for table A to extract fields necessary for the join and fields that are selected. (M blocks are read)

      3. Passing back Y entries (Y is greater than X in previous example).

      Actions on App server:

      1. Get back the result of the first select, write it to int. table in memory.

      2. Split the For All Entries select into many different selects with for example 5 entries for each key.

      Actions in Database for each of the small selects.

      4. Index access (primary key) to locate needed table entries of table B (O blocks read).

      5. Table access for table B (P blocks read)

      6. Passing back Z entries (Z is again greater than X as we assumed in our example ).

      Actions on App server:

      3. Get back the result of all smaller Selects from table B and write into int. table (with deleting possible duplicates).

      If we now compare DB actions in Approach 1 and Approach 2 we can see that DB does not really have less to do in the second case.

  • Posted on Jul 29, 2011 at 09:19 AM

    Please write ony comments if you know what you are talking about:

    + The sort of a FAE table itself does not improve performance, there is no prove for that.

    + the deletion of the duplicates does imporve the performance

    + actually I would recommend not to SORT, but to create a temporary hashed table only with the columns needed in the FAE and use a COLLECT

    SELECT matnr maktx spras 
                  FROM makt 
                  INTO TABLE it_makt
                  FOR ALL ENTRIES IN it_vlcvehicle_tmp
                  WHERE matnr EQ it_vlcvehicle_tmp-matnr.
                  * AND spras eq sy-langu.
    
    IF sy-subrc EQ 0.
      SORT it_makt BY matnr.
      DELETE ADJACENT DUPLICATES 
                   FROM it_makt COMPARING matnr spras.
    ENDIF.
    

    + Do not transfer more columns than actually needed, the last DELETE ADJACENT DUPLICATES can be saved as the FAE is distinct.

    + AND newer use different conditions in the SORT and in the DELETE !!!

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Siegfried Boes,

      As you have written above that,

      use a temporary Hashed table instead and use collect

      I would appreciate if you can provide an example for it so that it can be compared and your idea can be shared.

      Thanks,

      Harjeet

  • author's profile photo Former Member
    Former Member
    Posted on Jul 26, 2011 at 04:41 PM

    Hi,

    http://wiki.sdn.sap.com/wiki/display/ABAP/ABAPPerformanceand+Tuning#ABAPPerformanceandTuning-WhichisthebetterJOINSorSELECT...FORALLENTRIES...%3F

    Thanks,

    Manish

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 12, 2014 at 10:56 AM

    Bump again.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 09, 2015 at 09:27 AM

    Bumping again as new people are coming on the board all the time and continue perpetuating the myth.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 03:04 PM

    If there is any doubt about FAE, just do a SQL trace on what is generated and sent to the underlying database when you use FAE.

    The possibly thousands of individual SQL statements that are generated and executed one-by-one are many times more inefficient than a one SQL statement generated and executed using a JOIN.

    Educating one's self about "blocking factors" (well documented via Notes) using in conjunction with compound WHERE clauses will give you an idea about the pitfalls of FAE.

    Add a comment
    10|10000 characters needed characters exceeded

    • I don't know if it is good ABAP programming or not:


      But, long long time ago, I was facing very bad response time when using FAE. I had no option to use INNER JOIN because the internal table was supplied to me, from another source (not DB tables). So, I created a temp DB table, to store my internal table data and did a INNER JOIN using my temp table. Need I say more...?


      Thanks,

      Juwin

  • author's profile photo Former Member
    Former Member
    Posted on Jan 05, 2009 at 10:16 PM

    There are blogs that show this as well:

    [FOR ALL ENTRIES vs DB2 JOIN|http://it.toolbox.com/blogs/sap-on-db2/for-all-entries-vs-db2-join-8912]

    [Anyone Got Some Real Benchmark Stats on "For all Entries"???|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/4241] [original link is broken] [original link is broken] [original link is broken];

    [JOINS vs. FOR ALL ENTRIES - Which Performs Better?|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/6050] [original link is broken] [original link is broken] [original link is broken];

    ... and probably some others as well.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 13, 2009 at 07:08 PM

    I agree, inner and left joins even produce cleaner code. For all entries is for special cases where you already have some part of the selections done and can't change that part, or when dealing with middle processing before doing the next select.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 02, 2009 at 09:42 AM

    Hi ,

    as per my knowledge ,

    if u use for all entries , u are reading the database table with respect to internal table only . so, the access wud be easy ..

    but if u use inner join..u r reading the database table with respect to another db table..so it would affect ur performance ....

    thanks,

    Arun .

    Add a comment
    10|10000 characters needed characters exceeded

    • I don't recall the details of which db tables were used. The hardware was linux/oracle. 3 or 4 years ago.

      The tests were conducted on a loaded and unloaded test system several times over different parts of the day over several days - with the individual tests run in different orders. The results were consistent - internal table buffering was faster than relying on system buffering. Sufficient to convince even a deeply cynical skeptic like me that the variation was unlikely to be affected by any sensible* hardware or software variations.

      *Of course it is possible to build a pathalogically badly configured system where it would actually be slower, but I seriously doubt you'd encounter such in the real world.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.