topic Re: performance in Application Development and Automation Discussions

performance

Former Member — Fri, 08 Dec 2006 05:48:21 GMT

in performance view which is the better 'inner join' or 'for all entries'?

Re: performance

Former Member — Fri, 08 Dec 2006 05:51:26 GMT

hi sunil,

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.

<b>note: please reward all useful answers.</b>

regards,

siva

Re: performance

Former Member — Fri, 08 Dec 2006 05:54:40 GMT

1.i think for all entries is more efficient than joins.

2. but with for all entries complexity of coding when compared to joins but when ur using more than 3 table in join avoid to use join

3. for performance point of view for all entries uses more internal tables and joins uses more database tables. so to reduce database load , its better to go for for all entries

4.to fetch data from database table take more time tahn taking data from internal tables.

Re: performance

Former Member — Fri, 08 Dec 2006 05:57:37 GMT

hi,

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.

Re: performance

Former Member — Fri, 08 Dec 2006 06:48:18 GMT

If we see performance wise for all entries is much better than using joins statement.

Re: performance

Former Member — Fri, 08 Dec 2006 06:50:28 GMT

From the performance point of view Fo all Entreies will take lesser time than

Inner Join

Re: performance

Former Member — Fri, 08 Dec 2006 06:57:18 GMT

Hi,

I think it depends. There is no hard and fast rule as to which one is better. It depends upon the volume of data that is being selected.

Inner join works efficiently in few cases like join of VBAK, VBAP and VBEP i.e. tables which have sales orders. Here if you are trying to use for all entries in each case, it is definately time consuming.

The inner joins uses different strategy to get the data out where as the For all entries have different strategy.

While tuning, you need to check for both and then go for whichever is best.

Hope this helps.

Regards,

Shahu

Re: performance

Former Member — Fri, 08 Dec 2006 07:05:47 GMT

Hi Sunil,

They both are for two diffrenet purposes tough they may give the same result.

In case of for all entries : You need to retreive the data first and then use this retreived data to get another set of data .

But for all entries has the following drawbacks

1. It dumps if the number of entries in the internal table are very large

2. It does not allow duplicate entries , so it is always suggested to retrieve all the key fields while using for all entries.

In case of inner join , in one go you perform the steps which you do in for all entries .

The performance depends on the ampunt of data you retrieve , the fields you give(do you supply key fields) and on how many tables you perform join.

Reagrds

Arun

Re: performance

Former Member — Fri, 08 Dec 2006 07:51:26 GMT

hi Sunil,

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.

It is also possible to avoid nested SELECT loops by placing the selection from the outer loop in an internal table and then running the inner selection once only using the FOR ALL ENTRIES addition. This technique stems from the time before joins were allowed in the FROM clause. On the other hand, it does prevent redundant data from being transferred from the database.

=================================================

http://www.sap-img.com/abap/several-nested-inner-join-statements-can-be-inefficient.htm

Regards,

Santosh