topic Re: FOR ALL ENTRIES .... in Application Development and Automation Discussions

FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 14:01:51 GMT

What is the use of FOR ALL ENTRIES when compared to JOINS and what is the Precaution to take if u use FOR ALL ENTRIES ??????

If the ITAB is empty then wat happens if u use FOR ALL ENTRIES on that itab..???????

Thanks in Advance..

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 14:05:25 GMT

Hi,

To avoid nested select statements we use SELECT FOR ALL ENTRIES statement.

If there r more than 10000 records SELECT FOR ALL ENTRIES is used.

Performance wise SELECT FOR ALL ENTRIES is better to use.

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.

My advice is NOT to use INNER JOIN and FOR ALL ENTRIES together. It kills the performance,especially when key fields are not passed to WHERE clause.

First use FOR ALL ENTRIES on table AFKO with SELECT...ENDSELECT statement. And within this SELECT...ENDSELECT write SELECT query to hit AFPO.

check this link:

http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

Regards,

Satish

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 14:05:29 GMT

hi,

chk these threads...

https://forums.sdn.sap.com/click.jspa?searchID=8562094&messageID=4510851

https://forums.sdn.sap.com/click.jspa?searchID=8562094&messageID=4530377

https://forums.sdn.sap.com/click.jspa?searchID=8562094&messageID=4631616

regards,

priya.

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 14:12:22 GMT

Hi,

If ITAB is empty and we use it in FOR ALL ENTRIES addition, then it will lead to a full table scan on the data base table and tries to retrieve all the records from database table.

KindRegards,

khader.

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 14:13:07 GMT

Hi,

In a join, the tables (base tables) are combined to form one results table. The join conditions are applied to this results table. The resulting composite for an inner join logic contains only those records for which matching records exist in each base table.

Join conditions are not limited to key fields.

If columns from two tables have the same name, then you have to ensure that the field labels are unique by prefixing the table name or a table alias.

A table join is generally the most efficient way to read from the database. The database is responsible for deciding which table is read first and which index is used (DB Optimizer).

At LEFT OUTER JOIN, results tables can also contain entries from the designated left hand table without the presence of corresponding data records (join conditions) from the table on the right. These table fields are filled by the database with null values and are then initialized according to ABAP type.

It makes sense to use a LEFT OUTER JOIN when data from the table on the left is needed for which there are no corresponding entries in the table on the right.

The following limitations apply for the Left Outer Join:

you can only have a table or a view to the right of the JOIN operator, you cannot have another join statement

Only AND can be used as a logical operator in an ON condition.

every comparison in the ON condition must contain a field from the table on the right.

if the FROM clause contains an Outer Join, then all ON conditions must contain at least one 'true' JOIN condition (a condition that contains a field from tab1 and a field from tab2).

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .

Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

The easiest technical option for reading from multiple (dependent) tables is to use nested SELECT statements. The biggest disadvantage of this method is that for every data record contained in the external loop a SELECT statement is run using the database. This leads to a considerably worse performance in client/server systems.

I think you got some clear idea on this .

<REMOVED BY MODERATOR>

GAURAV J>

Edited by: Alvaro Tejada Galindo on Feb 4, 2008 5:00 PM

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 15:24:41 GMT

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

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 15:46:39 GMT

For all entries is performance wise better then the joins, so wherever desired use for all entries instead of joins.

Only precaution u need to take is that the table for which you are taking applying 'FOR ALL ENTRIES' should not be empty otherwise the select query will fetch all the values from the database table which can be a real performance issue.

So before making 'FOR ALL ENTRIES' for a table make a check that its not empty.

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 16:02:52 GMT

Ankit - this is not usually correct. Please see the blog I mentioned above.

Rob

Re: FOR ALL ENTRIES ....

Former Member — Mon, 04 Feb 2008 17:18:38 GMT

In a workshop given by SAP specialists we were told to use JOIN instead of FOR ALL ENTRIES, even if the JOIN is among 1000 tables, it will have better performance. That's what we were told....I don't know. I've read somewhere that when using JOIN the system can calculate the size of the result, hence manage memory and the other rources more specifically, but with the FOR ALL ENTRIES statement the system cannot estimate the result and won't size it's internal buffers properly.

FOR ALL ENTIRES should be used then the internal table has few entries, (less than 20).

One more thing, FOR ALL ENTIRES in Sql Server kills the database...(it makes an OR query for each internal table row)

Re: FOR ALL ENTRIES ....

Former Member — Tue, 05 Feb 2008 02:44:13 GMT

Hi,

Basically application performance can be broken down into two componets.

1) Good data retrieval, and

2) Good data manipulation/presentation.

Good data retrieval is acheived by pushing most if not all of the work onto the database engine, and retrieving only what is required. Hence statements like "Select *" should be avoided.

A inner join pushes the data retrieval to the database, where as a FOR ALL ENTRIES IN data is retreived from the database for the table and compared against the internal table on the application server.

It is best to join tables using their key fields or those used in an index otherwise a table scan is initiated resulting in the whole table being read by the database. This may be ok in development with small quantities of data, but can be disasterous in production.

However having said this some tables just can not be joined due to their design.

Hope this helps.