Skip to Content
avatar image
Former Member

about inner join's performance

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 06, 2007 at 05:57 AM

    <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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • Jul 05, 2007 at 07:11 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Jul 05, 2007 at 07:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 05, 2007 at 10:31 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 08, 2007 at 02:31 AM

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

    Add comment
    10|10000 characters needed characters exceeded