Avoiding inner joins????

[Former Member]

Hi all,

I was just thinking of ways to improve the perfomance of my report. Its fetching data in to two different tables using two innerjoins and these two internal tables are again looped for some other manipulation. Can anyone tell me the ways i can avoid innerjoin or is there other ways to improve psrfomance by avoiding innejoins in general?

Rakesh

[Former Member]

Hi Rakesh,

Use FOR ALL ENTRIES to avoid the Inner Join.

Paste your Inner Join Select Statement here, i will direct you how to use FOR ALL ENTRIES instead of joins.

Regards,

Satish

[Former Member]

hi sir i was able to find out that

Using several nested INNER JOIN statements can be inefficient and cause time out if the tables become too big in the future."

Joins here (in ABAP) are not those Native SQL Joins. If you are talking about the Core RDBMS, which mean Oracle or SQL Server, then Undoubtedly Joins are the best.

In ABAP, these joins are first split by the ABAP processor and then sent to the database, with the increase in DATA in production system, these joins tend to give way if your database keeps growing larger and larger.

You should rather used "FOR ALL ENTRIES IN" (Tabular conditions), which is a much effecient way as far as performance is concerned.

For example :

DATA: BEGIN OF LINE,

CARRID TYPE SPFLI-CARRID,

CONNID TYPE SPFLI-CONNID,

CITYFROM TYPE SPFLI-CITYFROM,

CITYTO TYPE SPFLI-CITYTO,

END OF LINE,

ITAB LIKE TABLE OF LINE.

LINE-CITYFROM = 'FRANKFURT'.

LINE-CITYTO = 'BERLIN'.

APPEND LINE TO ITAB.

LINE-CITYFROM = 'NEW YORK'.

LINE-CITYTO = 'SAN FRANCISCO'.

APPEND LINE TO ITAB.

SELECT CARRID CONNID CITYFROM CITYTO

INTO CORRESPONDING FIELDS OF LINE

FROM SPFLI

FOR ALL ENTRIES IN ITAB

WHERE CITYFROM = ITAB-CITYFROM AND CITYTO = ITAB-CITYTO.

WRITE: / LINE-CARRID, LINE-CONNID, LINE-CITYFROM, LINE-CITYTO.

ENDSELECT.

Do you have a ABAP Question?

Best regards,

SAP Basis, ABAP Programming and Other IMG Stuff

[Former Member]

Please see:

[JOINS vs. FOR ALL ENTRIES - Which Performs Better?|/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better]

Rob

[Former Member]

Hi,

There are two types of JOINS

INNER JOINS and OUTERJOINS

In the case of INNER JOIN only the records from tables for which the JOIN condition is met are retrieved.

In the case of OUTER JOIN you will get all the fields that are there in the LEFT table irrespective of the join condition.

Check the thread

Regards,

adesh

[Former Member]

I am reading and writing in the forum for over 2 years now, and these 'Avoid inner joins' recommendations come again and again.

Generally speaking, the JOIN is BETTER than any FOR ALL ENTRIES solution. The join does the same as the FOR ALL ENTRIES directly in the database wothout the blockwise transfer of the data.

Sometimes things go wrong with the join, then the problems can become more serve as with the FOR ALL ENTRIES.

This depends however 100% on the actually setting of SELECT, indexes, database optimizer and table content. And there

is not much to generalize. You can only analyse it in your setting.

Anyway, you should not look for the better of the bad solutions, but for the best solution and this should be the optimally working join. Therefore the WHERE-condition must be optimal and the indexes must support the accesses. Unfortunately the whole story needs more than just a few lines of explanation.

Siegfried

[Former Member]

Hi,

With all the inputs from experts,I think you should change your code from Inner Joins to For All Entries.

A example for the same would be as follows:

SELECT fields

FROM EKKO

INTO TABLE IT_EKKO

WHERE LIFNR IN SO_LIFNR

AND AEDAT IN SO_DATE

AND BSTYP EQ 'F'.

IF IT_EKKO[] IS NOT INITIAL.

*filter data from EKPO table,condition check on Purchasing document number,material & plant

SELECT fields

FROM EKPO

INTO TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN EQ IT_EKKO-EBELN

AND MATNR IN SO_MATNR

AND WERKS IN SO_WERKS.

else.

MESSAGE S000(DB) WITH TEXT-002.

ENDIF.

I hope you are able to get a solution to your query.

Thanks.

[Former Member]

>

> 
> With all the inputs from experts,I think you should change your code from Inner Joins to For All Entries.

Can you explain your reasoning??

Rob

[Former Member]

Hi Rakesh,

The essence of the discussion, conducted dozens of times on this forum, will be clear from the other replies: an inner join, if corectly designed, will give better performance than FOR ALL ENTRIES. The join really means that you ask the database to do the "for all entries" for you, without the application overhead.

However, when I write a join - except a really simple one - I always prototype it first using FOR ALL ENTRIES, because this makes it much easier to locate bad join conditions with SQL Trace. An SQL Trace on the join is normally not helpful because you don't get statistics at the level of the individual tables. So my advice is: design your logic with FOR ALL ENTRIES, trace and tune this, and then rewrite the lot to an inner join.

Hope this helps you,

Mark

[Former Member]

> An SQL Trace on the join is normally not helpful because you don't get statistics at the level of the individual tables.

is this really necessary?

What you need to know is the order of processing of the tables

and the index used for each table

This can be found in every Join explain.

Before you must analyse the optimal order of processing and the index which should be used.

Therefore the SE16 can be helpful,

SELECT ....
              FROM A
              INNER JOIN B
              ON  A~f1 = B~f1
              WHERE  A~f2 = '123'
              AND      B~f3 = 'abc'.

order A then B, there should be an index on A starting with f2 and an index on B with f1 and f3

order B then A, there should be an index on B starting with f3 and an index on A with f1 and f2

Check with SE16, which table is better suited to start with

SELECT ....
              FROM A
              WHERE  A~f2 = '123'.

SELECT ....
              FROM  B
              WHERE    B~f3 = 'abc'.

=> your expectation, this can be checked in SQL-Trace.

Siegfried

[Former Member]

> > An SQL Trace on the join is normally not helpful because you don't get statistics at the level of the individual tables.

> is this really necessary?

I guess there are various ways to go about this, prototyping using 'for all entries' and then sql-tracing the prototype has always done the job quite well for me. Anyway, a 2-table join probably doesn't need this approach, it is usually easy enough to figure out the best access by looking at the available indexes. A join over a large number of tables is a different matter though.

[Former Member]

> A join over a large number of tables is a different matter though.

but that is actually the problem, how do you determine the order of the FOR ALL ENTRIES?

You must know the order of the processing before you start the FOR ALL ENTRIES, so you can

try the join, if it uses the oorder and is fast then you done.

Analysis can be hard, your approach can help there.

There is simple argument for the Join, assume there are two select-options on table A and table B.

Both can be empty. If the condition on B is empty, the join can start with A. If the condition on A is empty,

the join can start with B. A FOR ALL ENTRIES is not that flexible.

Siegfried