about inner join's performance

[Former Member]

Hi:

In a select statement i used 7 inner join clauses.

But someone tell me I should not use so many inner join clauses and there should be at most 3 inner join clauses in a select statement.

It's another way to do the same work.Split more select statement from the one and then combin the internal tables.

I think the select statement that used 7 inner join clauses is optimized by system.It's performance is better than the other one through examination(se30).

I need your advises.Thanks.

[amit_khare]

As for the performance basis it is not suggested to use more than 3 (4 MAX) joins at a time in a single select statement.

Refer this link -

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Regards,

Amit

reward all helpful replies.

[Former Member]

hi:

Thanks your reply.

what is the evidence of "As for the performance basis it is not suggested to use more than 3 (4 MAX) joins at a time in a single select statement."

Regards.

[Former Member]

Hello Frank,

I would suggest that should not use more than 3 tables innerjoin,use for all entries for better performance.

Evidence i can say use ST05 to trace the performance.

or else use simple query to get proper time:

simple example :

data :a type i,

b type i,

c type i.

start-of-selection.

get runtime field a . " This is starting time.

• Write the join query's

get runtime field b. " Ending time

c = b - a.

write:/ c. " This is total time taken by query.

same thing you an use multiple query.

Thanks

Seshu

[Former Member]

Hi

keep the tables which are taking morwe time and then remove those tables from JOin condition.

move this table to a temp table and then remove all the duplicate entries

Then use For all entries for the same

it wll improve the performance

do some trail and error method here

regards

Shiva

[Former Member]

hi frank

inner join will surely decrease the performance of the program

but if u dnt want to avois it follw these steps

1> dnt use corresponding fields in your select statement

2> move the contents of your intrnal table to work area

3> analyse your code in sm30

4> use for all entries if you think you can change your code

5> if your requiremnet suggest you then use select single or select upto n rows

statement

6> try to use your where clause on primary keys

hope the information is helpful

thnkx

tc

[Former Member]

<b>Proper use of Inner Join</b>

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.

reward points if it is usefull ...

Girish

[Former Member]

But if i use more inner join,is it proper?

[Former Member]

Hi Frank,

You should not use more than 3 tables in JOIN. Why you might be getting good performance is because of the less data in test system, but when you go to production system you will find JOINING more than 3 tables is an issue.

So you should not join more than 3 tables.

Regards,

Atish

[Former Member]

I've done some work on this and have never seen any evidence that increasing the number of joins decreases performance. If you have to go against six or seven tables, it's going to take time no matter how you implement it. It's all about making efficient use of indices..

Views are implemented via joins and there are a number of standard SAP views with more than seven tables.

Rob

[Former Member]

I think if it is reality that there is at most 3 inner join in a select statement .I will feel sorrowful for abap and sap.It should be enhanced.

[Former Member]

Hi Frank,

ABAP has Internal tables.. You can say unique feature.

Thats why it is recommended to join less tables, so as to reduce the load at DB, and do the processing at application server using our own INTERNAL TABLES.

Regards,

Atish

[Former Member]

I am confused.Is there sap's experts?please talk about it.

[Former Member]

Hi Frank,

As all told you earlier, there can be more than 3 DB tables in the inner join, but it is not advisable as it will increase the load on DB and the query will be slower.

What are you confused with?

Regards,

Atish

[Former Member]

Two different voice.which one should i listen?

[Former Member]

Hi Frank,

See the use of Joins is totally depend on the tables which you are joining. why I said 3 tables as, we can consider it as the thumb rule so that one need not to worry about the no. of entries in the table, Indexes on the table, frequency of usage of the tables, buffering.

So there are so many factors.

There are lot many SAP views which contain more than 3 tables, but while creating those view all parameters are taken care.

Lets say you want to Join BKPF table, then it is not advisable to use more than 3 tables as it may contain the lots of entries.

So using the JOINS is depend on many factors so thats why most people suggest better to use 3 or 4 tables,.

I Hope now it's clear

Regards,

Atish

[Former Member]

I think that u said is reasonable.Thanks your reply.

[Former Member]

Check these:

/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

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

Rob