inner join vs for all entries

[Former Member]

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

[matt]

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.

[ThomasZloch]

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.

[former_member219762]

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 .

[matt]

Of course with HANA there are no cluster or pooled tables, so the need for FAE is even less.