topic Re: diff between innerjoin and forallentries in Application Development and Automation Discussions

diff between innerjoin and forallentries

Former Member — Wed, 13 Dec 2006 18:34:01 GMT

what is innerjoin and forallentries ? diff between innerjoin and forallentries?

Re: diff between innerjoin and forallentries

ferry_lianto — Wed, 13 Dec 2006 18:40:36 GMT

Hi,

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus:

- Large amount of data

- Mixing processing and reading of data

- Fast internal reprocessing of data

- Fast

The Minus:

- Difficult to program/understand

- Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

- Removing duplicates from the driver table

- Sorting the driver table

- If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Hope this will help.

Regards,

Ferry Lianto

Re: diff between innerjoin and forallentries

Former Member — Wed, 13 Dec 2006 18:48:15 GMT

HI,

Inner join is basically the intersection of two sets based on certain condition.

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

<b>For all entries</b>

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Regards,

Vara

Message was edited by:

varaprasad bhagavatula

Re: diff between innerjoin and forallentries

Former Member — Wed, 13 Dec 2006 19:00:37 GMT

I m nt really sure whether i got it right myself...........however from what I understand simply put its the following:

Inner Join between the two tables will compare the common entries in the field mentioned and then display them,

however when using for all entries, all the occurences of the entry table from the second table will be displayed

If i m wrong then i wld appreciate if anyone of the others can correct. thanks

Janak

Starters so dnt be too critical