Skip to Content
author's profile photo Former Member
Former Member

inner join vs for all entries

hi experts,

i have done SQL Trace on select statements using joins and for all entries.

case1: using joins and time taken : 2.249.936

case2: using for all entries and time taken : 3.074.469

Clearly i can see that join is bit faster than for all entries.But i would like to know in which cases for all entries is faster than joins

and the cases in which joins are faster than for all entries.

i can see data being fetched from only KNA1 table in ST05 but i get data from KNB1 even.Without a fetch on KNB1,how am i getting data from

KNB1 using joins.??

Please advise

TABLES: kna1.

SELECT-OPTIONS:s_kunnr for kna1-kunnr.

data: BEGIN OF it_cust OCCURS 0,

kna1 type kna1,

knb1 type knb1,

END OF it_cust.

data: it_kna1 type STANDARD TABLE OF kna1,

it_knb1 type STANDARD TABLE OF knb1.

*using joins

select * INTO TABLE it_cust from kna1 INNER JOIN knb1 on kna1~kunnr = knb1~kunnr

where kna1~kunnr in s_kunnr.

*time taken : 2.249.936

*using for all entries

SELECT * from kna1 INTO TABLE it_kna1 where kunnr in s_kunnr .

if not it_kna1 is INITIAL.

SELECT * from knb1 into TABLE it_knb1 FOR ALL ENTRIES IN it_kna1 WHERE kunnr = it_kna1-kunnr .

endif.

*time taken : 3.074.469

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Jun 05, 2013 at 08:08 PM

    If you use EXPLAIN SQL from within the ST05 trace, it will give you more information about the SQL and the way it is processed. Try it and see.

    Generally, use INNER JOIN. Only use FAE is one or more table is a cluster or pool table, or you've tried all other optimisations and still have performance problems. (Also in BW start/end routines).

    It is a myth that FAE is usually better than INNER JOIN. The fact is, as you've shown, that FAE is usually worse than INNER JOIN - but you may have a hard time arguing it. The myth is so entrenched.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2013 at 08:17 AM

    It's not only about which one is faster.

    Major advantages of JOIN constructs are the flexibility of the access path (sequence of table accesses) based on runtime selection criteria (which can make a join exponentially faster than FAE with its fixed access path) and that your result ends up in one internal table, which is very often the requirement. You need much less code when using Joins.

    FAE can come in handy e.g. when cluster/pool tables are involved, or very long lists of single selection values would make the SQL statement grow too large if used as a range for the IN-operator, or if the results are indeed required in separate internal tables.

    The buffering issue is more complex. In case of many repeated accesses to few or single lines I would also avoid joins on buffered tables, however if I just read a reasonable amount of data once for list output, I have seen no significant disadvantage when buffered tables are involved (G/L account master data tables, for example).

    So after many years of comparing and experimenting, I came to the conclusion to use Joins whenever possible, and use FAE in certain circumstances. You can have both in one statement as well.

    What Matt and I and others stand up against is the redundant copy/pasting and repeating of simple and misleading statements like "Use FAE instead of joins, it will be faster".

    Thomas

    Edit: there is of course a redundancy in the result set of a join, in a header/item relationship the header fields are being repeated for each item.

    In a scenario with many header fields, few item fields but very many item rows this might become a noticable factor, however I have not witnessed an adverse effect by this in my practice so far.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 10, 2013 at 08:54 PM

    SELECT .. FOR ALL ENTRIES was created in OPEN SQL at a time when it was not yet possible to perform database JOINs (this was not supported for all Sap-approved DBMS). At that time For all entries was created to solve problem of nested selects.For all entries bundle the select statements according to parameter rsdb/max_blocking_factor. So it advantage over nested selects.

    For joins database optimizer dynamically determines the order of access for ABAP joins from the database optimizer.The criterion for the order of access is to have as few data blocks to be read as possible. There is no way to control this from the application program (apart from using database hints).

    So Joins generally gives better performance over FAE.

    But some cases FAE gives better performance like if you query the data from same tables more than once in your program ,in that case FAE may give better performance by using buffer (Joins by pass the buffer). FAE is useful to access pooled and cluster tables .

    Add a comment
    10|10000 characters needed characters exceeded

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.