topic Re: about inner join's performance in Application Development and Automation Discussions

about inner join's performance

Former Member — Thu, 05 Jul 2007 07:08:01 GMT

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.

Re: about inner join's performance

amit_khare — Thu, 05 Jul 2007 07:11:29 GMT

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.

Re: about inner join's performance

Former Member — Thu, 05 Jul 2007 07:15:01 GMT

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

Re: about inner join's performance

Former Member — Thu, 05 Jul 2007 10:31:18 GMT

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

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 01:07:07 GMT

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.

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 01:13:25 GMT

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

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 05:57:07 GMT

<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

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 08:14:40 GMT

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

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 08:17:02 GMT

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

Re: about inner join's performance

Former Member — Fri, 06 Jul 2007 13:12:25 GMT

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

Re: about inner join's performance

Former Member — Sat, 07 Jul 2007 12:35:02 GMT

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.

Re: about inner join's performance

Former Member — Sat, 07 Jul 2007 12:50:23 GMT

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

Re: about inner join's performance

Former Member — Sun, 08 Jul 2007 02:31:55 GMT

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

Re: about inner join's performance

Former Member — Sun, 08 Jul 2007 02:35:44 GMT

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

Re: about inner join's performance

Former Member — Sun, 08 Jul 2007 03:05:13 GMT

Two different voice.which one should i listen?

Re: about inner join's performance

Former Member — Sun, 08 Jul 2007 03:10:16 GMT

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

Re: about inner join's performance

Former Member — Sun, 08 Jul 2007 03:28:03 GMT

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

Re: about inner join's performance

Former Member — Mon, 09 Jul 2007 13:11:14 GMT

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