Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

matt
Active Contributor
0 Kudos

I quote from Siegfried Boes' excellent post here:

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

46 REPLIES 46

Former Member
0 Kudos

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.

0 Kudos

To give food for thought here's an example I gave in a thread:

If you have a statement like

SELECT ... FOR ALL ENTRIES IN FAE_itab WHERE f = FAE_itab-f.

SAP sends it to the database depending how the parameter rsdb/prefer_union_all is set:

rsdb/prefer_union_all = 0 =>

 SELECT ... WHERE f = FAE_itab[1]-f
          OR    f = FAE_itab[2]-f
          ...
          OR    f = FAE_itab[N]-f

You have some influence of the generated statement type: Instead of OR'ed fields an IN list can be used

if you have only a single coulmn N to compare:

rsdb/prefer_in_itab_opt parameter:

SELECT ... WHERE f IN (itab[1]-f, itab[2]-f, ..., itab[N]-f)

rsdb/prefer_union_all = 1 =>

SELECT ... WHERE f = FAE_itab[1]-f
UNION ALL SELECT ... WHERE f = FAE_itab[2]-f
....
UNION ALL SELECT ... WHERE f = FAE_itab[N]-f

see: Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement

As you can see for the 2nd parameter several statements are generated and combined with a UNION ALL,

the first setting generates statements with OR's (or uses IN if possible) for the entries in FAE_itab.

I give you a little example here (my parameters are set in a way that the OR's are translated to IN lists; i traced the execution in ST05)

Select myid into table t_tabcount from mydbtable
  for all entries in t_table    " 484 entries
    where myid = t_table-myid . 

ST05 trace:


|Transaction SEU_INT|Work process no 0|Proc.type  DIA|Client  200|User |
|Duration |Obj. name |Op.    |Recs.|RC    |Statement|
| 640|mydbtable |PREPARE|   |  0|SELECT WHERE "myid" IN ( :A0 , :A1 , :A2 , :A3 , :A4 ) AND "myid" = :A5|
| 2|mydbtable |OPEN   |   |  0|SELECT WHERE "myid" IN ( 1 , 2 , 3 , 4 , 5 ) AND "myid" = 72 |
| 2.536|mydbtable |FETCH  |    0|  1403|   |
| 3|mydbtable |REOPEN |   |  0|SELECT WHERE "myid" IN ( 6 , 7 , 8 , 9 , 10 ) AND "myid" = 72 |
| 118|mydbtable |FETCH  |  0|  |
| 2|mydbtable |REOPEN |  |  0|SELECT WHERE "myid" IN ( 11 , 12 , 13 , 14 , 15 ) AND "myid" = 72     |
... 
| 3|mydbtable |REOPEN |  |  0|SELECT WHERE "myid" IN ( 475 , 476 , 477 , 478 , 479 ) AND "myid" = 72  |
| 94|mydbtable |FETCH  | 0| 1403|   |
| 2|mydbtable |REOPEN |   |  0|SELECT WHERE "myid" IN ( 480 , 481 , 482 , 483 , 484 ) AND "myid" = 72 |

You see the IN list contained 5 entries each , wich made up about 97 statements for all 484 entries.

For every statment you have a single fetch operation wich means a separate access to the database.

If you would replace the FAE with a join you would only have one fetch to the database.

With the example above we can derive these observations:

1. From database point of view these settings kill performance when you access a big table and/or have a lot of entries or columns in your FAE_itab. Furthermore, you hide information what data you will access

at all and thus you block the database from creating a more efficient execution plan because it DOESN'T KNOW wich data you will select in the next step. I.e. it may be more efficient to scan the table in one shot instead of having many index accesses - but the database can make this decision only if it can examine ONE statement that has ALL the information of what data to retrieve.

2. A second impact is that with every statement execution you trigger the allocation of database resources

wich will contribute to the overhead described above.

Said that, FAE can never be a replacement for joining big tables (think of having a table with thousands of records in a FAE table )

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

0 Kudos

It will work till a certain limit. You cant have a range table when you have data more than what your system permits, say 1000 records. check the following [thread|]

0 Kudos

>

> It will work till a certain limit. You cant have a range table when you have data more than what your system permits, say 1000 records. check the following [thread|]

Hi ZAFCO,

I don't think here we discuss about range and for all entries.

Its all about for all entries and join statement.

Regards,

Abraham

0 Kudos

What you say deserves some investigation. I know when dealing with a main table over 110million, and the 'for all entries' table is approx 6million... I had to resort to the RFC method. See performance indicators in said document, page 7.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/abap/performance%2...

Thank You,

Dan P.

0 Kudos

can u let me know what is a for all entries use ? and how can i use it when working with 3 or 4 tables?

what is better in this case of working with 3 or 4 tables?

matt
Active Contributor
0 Kudos

You don't need to know what FOR ALL ENTRIES is for, because it's usually better to use INNER JOIN. Since INNER JOINS were first introduced I have never used FOR ALL ENTRIES in its place.

That's 13 years!

I've successfully and easily used INNER JOIN selecting from 4 or more tables. That's what you should do.

Juwin
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Former Member
0 Kudos

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 .

0 Kudos

Hi,

i am really interested in ways of boosting performance but unfortunately everyone seems to have some theory why something should be faster or slower. I deal with performance optimization, not whole of my life but regularely. I had to find out that in many cases reality beats theory. similar to a post above i thought getting higher package size in for all entries in would enhance performance - but i had to see selects having the same avarage time per row no matter if package size was 5, 10 , 50, 100, 500, 1000, 10000 or 50000 - this is completely against all theory but reality did not care.

Furthermore I had to learn that testing performance always requires setting up a valid scenario. What does any performance statistic say if noone posts the sourroundings? When I seriously test performance for mass data reading I make one select on a high populated production server, then I wait a whole day to get the caches and buffers for statements, calls, indexes and whatever db system and application sevrer contain back to a ground level and then tast on a comparable basis. Everything else is just nonsense as calling a statement twice can differ in runtime by factor 10 or even more. So also calling different statements in row can be massively influence each other by filling caches and buffers.

And here is a point I'd really like to have an test data based evaluation on. Inner Join always bypasses table buffers as far as i learned while FAE is not if you only use one field of the table in the where clause. If the statement is rarely used and very unlikely to be in buffers join might be faster, but what about a very frequently called statement which has high caching propability - is then join still making up the factor 10 or more from the cache? Is there any break even of technology?

Roman

matt
Active Contributor
0 Kudos

Even the benefit of buffering disappears once you're using HANA.

0 Kudos

The benefit can shrink but will not completely disappear.

However, table buffering helps to avoid also the network roundtrips between application server and database, so some benefit remains for sure.

matt
Active Contributor
0 Kudos

To an extent. However, it really isn't as significant at all as it used to be.

0 Kudos

Hello Matthew,

the link (correctly) tells that minimizing the number of database transfers is even more important for HANA.
Table buffering and SELECT FOR ALL ENTRIES are both ways achieve this.
However, ABAP table buffering takes place in the shared memory of the application server instance, and in case of frequently accessed tables where the contents get rarely changed, you can achieve almost 0 database transfer per transaction.

With SELECT FOR ALL ENTRIES you will always need some database transfer (depending on instance parameters rsdb/max*, see SAP Note 48230.

Actually there are cases when table buffering can yield the highest performance gains.
SELECT FOR ALL ENTRIES can be combined with table buffering, as long as the query otherwise does not bypass table buffering and the key in a single row in the driver table identifies a single buffer area. This combination is probably the best approach.

Maybe you are not recognizing the relevance of table buffering because you don't notice when the accesses are buffered?

For playing around, I would suggest to try out in a sandbox system

1. SELECTS in a LOOP

2.  FAE

3. INNER JOIN

for database tables T005 + T005T + T005X for example, to display country name and time format for all English speaking countries for example...
Repeat the tests after switching off table buffering for these tables. Don't try this in a productive system.

matt
Active Contributor
0 Kudos

I buffer data in internal tables. I never rely on appserver buffering. This was after running tests that showed internal table buffering was better performing. In my view it's also better programming, as you shouldn't rely on system tuning to ensure your program performs adequately.

0 Kudos

Internal table performance with an appropriate key is of course better than table buffering.

I have seen situations when even the first access to fill the application buffers became critical. In that case table buffering can make a big difference.

I have seen situations when the two approach had to be combined to reach sufficient perfrormance take from internal table if possible,, if it is not found there, go and use the table buffer (or in case of access to ABAP Dictionary, the special buffering function modules).

With no details on the test environment or on the test case it is hard to comment on your test results.

If there are some tight performance KPIs given, you might need to rely on system tuning as well. Of course you need a good design of the software first.

matt
Active Contributor
0 Kudos

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.

matt_ellicott
Explorer
0 Kudos

i have seen instances where one is better than the other and vis-versa. it depends on the size of the tables, the relationship that exist between the table and the key's being used to access the data.

if your talking about a join of vbak and vbap with vbeln as the key, then yes an inner join would be the solution. if your talking about joining vbap and mara with 100 million sales orders and 9 million materials then the FAE works better. The programmer needs to understand the scope of what info they are returning and the relationship between the tables involved. I tend to run queries with each of the tables using the keys to see how they respond and then decide from there if i'm going to use a join or an FAE.

0 Kudos

Hi to All,

See the below code and execute on ur System.

You get idea which is better one...


REPORT ZTEST_TYPE_GROUP NO STANDARD PAGE HEADING.

TABLES : EKKO , EKPO , LFA1.

TYPES : BEGIN OF TY_EKKO,
        EBELN TYPE EKKO-EBELN,
        BUKRS TYPE EKKO-BUKRS,
        BSTYP TYPE EKKO-BSTYP,
        BSART TYPE EKKO-BSART,
        LIFNR TYPE EKKO-LIFNR,
        END OF TY_EKKO.

TYPES : BEGIN OF TY_EKPO,
        EBELN TYPE EKPO-EBELN,
        EBELP TYPE EKPO-EBELP,
        MATNR TYPE EKPO-MATNR,
        WERKS TYPE EKPO-WERKS,
        LGORT TYPE EKPO-LGORT,
        END OF TY_EKPO.

TYPES : BEGIN OF TY_LFA1,
        LIFNR TYPE LFA1-LIFNR,
        NAME1 TYPE LFA1-NAME1,
        END OF TY_LFA1.

TYPES : BEGIN OF TY_FINAL,
        EBELN TYPE EKKO-EBELN,
        BUKRS TYPE EKKO-BUKRS,
        BSTYP TYPE EKKO-BSTYP,
        BSART TYPE EKKO-BSART,
        LIFNR TYPE EKKO-LIFNR,
        EBELP TYPE EKPO-EBELP,
        MATNR TYPE EKPO-MATNR,
        WERKS TYPE EKPO-WERKS,
        LGORT TYPE EKPO-LGORT,
        END OF TY_FINAL.

DATA : IT_EKKO TYPE STANDARD TABLE OF TY_EKKO,
       IT_EKPO TYPE STANDARD TABLE OF TY_EKPO,
       IT_LFA1 TYPE STANDARD TABLE OF TY_LFA1.

DATA : WA_EKKO TYPE TY_EKKO,
       WA_EKPO TYPE TY_EKPO,
       WA_LFA1 TYPE TY_LFA1.

DATA : IT_FINAL1 TYPE STANDARD TABLE OF TY_FINAL,
       IT_FINAL2 TYPE STANDARD TABLE OF TY_FINAL,
       IT_FINAL3 TYPE STANDARD TABLE OF TY_FINAL.

DATA : WA_FINAL1 TYPE TY_FINAL,
       WA_FINAL2 TYPE TY_FINAL,
       WA_FINAL3 TYPE TY_FINAL.

*SELECTION-SCREEN : BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : S_BUKRS FOR EKKO-BUKRS .
SELECT-OPTIONS : S_WERKS FOR EKPO-WERKS .
SELECT-OPTIONS : S_LIFNR FOR LFA1-LIFNR.

*SELECTION-SCREEN : END OF BLOCK B1.

INITIALIZATION.

START-OF-SELECTION.

  DATA: START       TYPE I,
          END       TYPE I,
          DIF       TYPE I.

  CLEAR : START , END , DIF.

  GET RUN TIME FIELD START.

  SELECT  A~EBELN A~BUKRS A~BSTYP A~BSART A~LIFNR
          B~EBELP B~MATNR B~WERKS B~LGORT
     FROM EKKO AS A INNER JOIN EKPO AS B
        ON A~EBELN = B~EBELN
        INTO TABLE IT_FINAL1
         WHERE A~BUKRS in S_BUKRS
          AND  B~WERKS in S_WERKS
          AND  A~LIFNR in S_LIFNR.

  GET RUN TIME FIELD END.
  DIF = END - START.

  WRITE: /001 'Time for Join (Header to Item)' ,
          055 ':', DIF, 'microseconds'.

  CLEAR : START , END ,DIF.

  GET RUN TIME FIELD START.

  SELECT  A~EBELN A~BUKRS A~BSTYP A~BSART A~LIFNR
         B~EBELP B~MATNR B~WERKS B~LGORT
    FROM EKPO AS B INNER JOIN EKKO AS A
       ON A~EBELN = B~EBELN
        INTO TABLE IT_FINAL2
        WHERE A~BUKRS in S_BUKRS
         AND  B~WERKS in S_WERKS
         AND  A~LIFNR in S_LIFNR.

  GET RUN TIME FIELD END.

  DIF = END - START.


  WRITE: /001 'Time for Join (Item to Header)' ,
          055 ':', DIF, 'microseconds'.

  CLEAR : START , END ,DIF.

  GET RUN TIME FIELD START.

  SELECT EBELN BUKRS BSTYP BSART LIFNR FROM EKKO
     INTO TABLE IT_EKKO
        WHERE BUKRS IN S_BUKRS
          AND LIFNR IN S_LIFNR.
  IF IT_EKKO IS NOT INITIAL.
    SORT IT_EKKO BY EBELN.
    SELECT EBELN EBELP MATNR WERKS LGORT FROM EKPO
      INTO TABLE IT_EKPO
          FOR ALL ENTRIES IN IT_EKKO
              WHERE EBELN = IT_EKKO-EBELN
                AND WERKS IN S_WERKS.
  ENDIF.

  LOOP AT IT_EKPO INTO WA_EKPO.
    READ TABLE IT_EKKO INTO WA_EKKO WITH KEY EBELN = WA_EKPO-EBELN BINARY SEARCH.
    IF SY-SUBRC = 0.
      WA_FINAL3-EBELN  = WA_EKKO-EBELN.
      WA_FINAL3-BUKRS  = WA_EKKO-BUKRS.
      WA_FINAL3-BSTYP  = WA_EKKO-BSTYP.
      WA_FINAL3-BSART  = WA_EKKO-BSART.
      WA_FINAL3-LIFNR  = WA_EKKO-LIFNR.
      WA_FINAL3-EBELP  = WA_EKPO-EBELP.
      WA_FINAL3-MATNR  = WA_EKPO-MATNR.
      WA_FINAL3-WERKS  = WA_EKPO-WERKS.
      WA_FINAL3-LGORT  = WA_EKPO-LGORT.
      APPEND WA_FINAL3 TO IT_FINAL3.
      CLEAR : WA_FINAL3.
    ENDIF.
    CLEAR : WA_EKKO , WA_EKPO.
  ENDLOOP.

  GET RUN TIME FIELD END.

  DIF = END - START.

  WRITE: /001 'Time for For All Entries in',
          055 ':', DIF, 'microseconds'.

END-OF-SELECTION.

Regards,

Vipul Darji

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

0 Kudos

Dear Vipul

It was nice on your part to illustrate the demonstration of Joins Vs FAE. However, I just swapped some lines of your source code and found that "FOR ALL ENTRIES" ended up on the loosing side as compared to Joins. I mean when the lines of code suporitng FAE is executed before INNER JOIN source code, system says it takes more time and vice versa.

Any answers???

I believe the performance of INNER JOINS and FOR ALL ENTRIES varies depending upon underlying databases. SAP also recommends this and have relesed an informative note as well.

Regards,

Rupesh

Former Member
0 Kudos

Speaking for the use of for all entries...

Yes use it when the combined join shows an execution profile that goes crazy.

But if you do use for all entries you need to make sure that the internal table is never empty or initial.

I used to always put the for all entries into a perform and the first few lines in the perform verified that the table was not empty.

Both for all entries and the inner / outer joins have their uses.

Experience tells you also that when using joins over 5 tables or more the execution plan can get quite dicey and sometimes even very counterproductive. This is usually where the for all entries brings the most bang for the buck.

Create your index using the join over 3 tables and fetch the matching entries in the last 2 using for all entries in...

Have fun.

0 Kudos

When you use For all entries, You can improve the performance by sorting the for all entries ITAB by the keys used in the where condtiontion.

MOVE the FAE ITAB to Temporarty ITAB and do the sort and delete of the FAE Temporary ITAB with the keys used in the where condition. We can see the improvement in the performance of FAEs. Duplicates in the FAE Temporary internal table will be deleted which will lead to lesser number of selects in the Data base.

For eg :

*-Derive material description text

REFRESH : it_vlcvehicle_tmp .

it_vlcvehicle_tmp = it_vlcvehicle.

SORT it_vlcvehicle_tmp BY matnr.

DELETE ADJACENT DUPLICATES FROM it_vlcvehicle_tmp COMPARING matnr.

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.

Best Regards,

Senthilraj Selvaraj

Former Member
0 Kudos

Against FAE.

Having lots of entries in the internal table in the FAE leads to lot of loops between the Application server and the Database server and subsequent network hogging. More entries in the internal table also leads to the RSQL error many times.

bilen_cekic
Explorer
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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.

Former Member
0 Kudos

Hi,

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

Thanks,

Manish

former_member194613
Active Contributor
0 Kudos

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

0 Kudos

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

matt
Active Contributor
0 Kudos

Just thought I'd give this another airing, since there has been a spate of posts that promote the myth of FAE being better than INNER JOIN. It isn't in most cases.

Former Member
0 Kudos

This seems to be a topic we all seem very passionate about.

I agree with Matt, that running queries with the trace on is the only way to find the correct solution.  I have experienced FAE working very well in some circumstances and terribly in others. 

It seems to be different for different databases, ORACLE seems to work well, but DB2 seems to struggle.

Volumes of data and the selection criteria we are using all play a part.

As has been mentioned, parameter settings can change the efficiency, but how many of us have the luxury of being able to ask for system changes to optimise their report.

And of course when it goes live and runs like a dog, we just say "Well it worked okay in DEV".

Former Member
0 Kudos

Hi Matthew,

              I have a requirement.

vbeln is to be selected through parameters from vbak.and then going through vbap and vbpa to kna1  ship to party(kna1) and  vbeln are to be  displayed in the final table.This requirement is to be fulfilled by means of joining 4 internal tables only.

The structure of the final internal table in which output is to be displayed is as follows.

TYPES: begin of ty_final,
      vbeln TYPE vbak-vbeln,
      name1 TYPE kna1-name1,
   END OF ty_final.

Please advice,

Vinit.

matt
Active Contributor

Former Member
0 Kudos

Hi matthew,

                I have a doubt , By using the for all entries in select query can we get duplicate entries into target table.

Regards,

krishna .

matt
Active Contributor
0 Kudos

Yes - you can get duplicates. However - use INNER JOIN. 99% of the time it's better.

0 Kudos

the FOR ALL ENTRIES will always remove duplicate of the resulting set, so it is best practices to always extract whole primary keys of extracted from tables. (keeping two items of  same docuemnt with same value for example, so keep item number in extraction)

Regards,

Raymond

matt
Active Contributor
0 Kudos

Bump again.