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: 

Difference Between join & for all entries

Former Member
0 Kudos
2,395

Could anyone help me out between the difference of inner join & for all entries in select statement? When will we use which of them?

6 REPLIES 6

Former Member
0 Kudos
856

INNER JOIN - This is used to join two different DATABASE tables.

FOR ALL ENTRIES - This is used to avoid the SELECT inside a loop. Usually if we want the child details for all the header records of a internal table then you loop at the header table and fire the SELECT. We can avoid the SELECT inside the looop using the FOR ALL ENTRIES clause.

Hope this clarifies.

Regards,

Ravi

Note : Please mark all the helpful answers

0 Kudos
856

Abap programers and most of the dba's that support abap programmers , are familiar with the abap clause "for all entries".

Most of the web pages I visited recently, discuss 3 major drawbacks of the "for all entries" clause :

1. duplicate rows are automatically removed

2. if the itab used in the clause is empty , all the rows in the source table will be selected .

3. performance degradation when using the clause on big tables.

In this post I'd like to shed some light on the third issue,

Specifically , i'll discuss the use of the "for all entries" clause as a means to join tables in the abap code instead of in db2.

Select * from mara

For all entries in itab

Where matnr = itab-matnr.

If the actual source of the material list (represented here by itab) is actually another database table , like :

select matnr from mseg

into corresponding fields of table itab

where ….

Then you could have used one sql statement that joins both tables.

Select t1.*

From mara t1, mseg t2

Where t1.matnr = t2.matnr

And T2…..

So what are the drawbacks of using the "for all entires" instead of a join ?

At run time , in order to fulfill the "for all entries " request, the abap engine will

generate several sql statements (for detailed information on this refer to note 48230).

Regardless of which method the engine uses (union all, "or" or "in" predicates)

If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop.

This rerun of the same sql statement , each time with different host values ,

is a source of resource waste because it may lead to re-reading of data pages .

returing to the above example , lets say that our itab contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time. Select * from mara

Where matnr in ( ...)

Db2 will be able to perform this sql statement cheaply all 50 times , using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will see that some of the data pages , especially the root and middle-tire index pages have been re-read each execution .

Even though db2 has mechanisms like buffer pools and sequential detection to try to minimize the i/o cost of such cases , those mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory.

Had you coded the join , db2 would have known that you actually need 500 rows from mara, it would have been able to use other access methods , and potentially consume less getpages i/o and cpu .

In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application.

Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.

In conclusion

I suggest to "think twice" before using the "for all entries" clause and to evaluate the use of database views as a means to :

a. simplify sql b. simplify abap code c. get around open sql limitations.

Also check out

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

http://www.sswug.org/see/28439

http://web.mit.edu/ist/org/admincomputing/dev/abap_review_check_list.htm

http://www.sapgenie.com/abap/performance.htm

Hope this’ll give you idea!!

P.S award the points.!!! !!!

Former Member
0 Kudos
856

HI Vishal,

For ALL ENTRIES IN table will carry all the possible entries in the table specified for taking the data for particular table.

While inner JOIN will take all the possible entries in both the tables for the specified where clauses.

regards,

Brijesh Patel

0 Kudos
856

FOR ALL ENTRIES retrieves all the table entries depending on another table where as Inner Join retrieves table entries which match the specified condition between two tables.Do not use JOIN if the number of tables is greater than 3 as the performance will be reduced.

Regards,

Sangram

SAP Consultant

Former Member
0 Kudos
856

Hi,

Joins in the FROM Clause

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.

<b>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.</b> 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.

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: This variant is as follows:

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 andIN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

<b>You can use the option FOR ALL ENTRIESto replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.</b>

Thanks and Regards,

Bharat Kumar Reddy.V

Message was edited by: Bharat Reddy V

Former Member
0 Kudos
856

Hi Vishal,

Views (inner join) are a fast way to access information from multiple tables. Be aware that the result set only includes rows that appear in both tables.

Take a look at this code,

loop at t_proj.

select objnr pspnr posid stufe fakkz zbillt

psphi zend ztermcd

from prps into t_prps

"place row into header of t_prps

where psphi eq t_proj-pspnr

"prps project = project rsn

and zbillt eq p_billt.

"billing type input param (01)

append t_prps.

"append header to body of table

endselect.

"notice no keyword table which is why the endselect

if sy-subrc <> 0.

write t_proj-pspnr to w_posid.

"convert to wbs element ext #

w_error = 'Project & with no WBS element'(002).

replace '&' with w_posid(7) into w_error.

write w_error.

endif.

endloop.

"loop through all the billable projects

For all entries faster than appending

Instead of appending (as in above example) do the following (but make sure unique key is in select clause):

select objnr pspnr posid stufe

fakkz zbillt psphi zend ztermcd

"pspnr is unique

from prps into table t_prps

"place row into header of t_prps

for all entries in t_proj

"all billable projects in internal table

where psphi eq t_proj-pspnr

"prps project = project rsn

and zbillt eq p_billt.

"billing type input param (01)

<b>3 pitfalls to avoid</b>

1) This is the equivalent of saying "select distinct shkzg wrbtr saknr"

It will only pick up one line item if multiple line items appear

with the same debit/credit indicator, amount and GL Account.

If you want all occurrences of these you must have a select statement

that includes the table's unique key, also called primary key. In the

data dictionary (SE11) the fields belonging to the unique key are marked with

an "X" in the key column.

2) FOR ALL ENTRIES IN...acts like a range table, so that if the "one" table is empty, all rows in the "many" table are selected

3) If the parent table (t_proj) is very large there is performance degradation

Instead loop through parent table(t_proj), appending to child table (t_prps)

If found helpful, please do reward.