Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Best way to JOIN 3 tables into internal table ?

0 Kudos
972

Hi friends,

i have the following issue:

i need to join information of 3 different tables ( BUT000, BUT020 and ADRC ) concerning Business Partners and i need them into one internal table.

how would this be achieved with best performance ?

regards,

CL

1 ACCEPTED SOLUTION

Former Member
0 Kudos
370

Hi,

Try this:

select <fld_list> into table itab
from but000 as a 
inner join but020 as b on a~partner = b~partner
inner join adrc as c on b~addrnumber = c~addrnumber.

8 REPLIES 8

Former Member
0 Kudos
370

Hi, Clemens,

Kindly find the following code for getting BPs from the inner join of the mentioned three tables.

-


  • Internal table for business partners

TYPES: BEGIN OF LS_PARTNER,

PARTNER TYPE BU_PARTNER,

END OF LS_PARTNER.

DATA: LT_PARTNER TYPE TABLE OF LS_PARTNER.

  • Select the BPs from the tables

SELECT BUT020~PARTNER

INTO TABLE LT_PARTNER

FROM BUT020

INNER JOIN BUT000 ON BUT000PARTNER = BUT020PARTNER

INNER JOIN ADRC ON ADRCADDRNUMBER = BUT020ADDRNUMBER.

IF SY-SUBRC EQ 0.

  • Do the processing of the data

ENDIF.

-


Hope this sort out your query.

PS If the answer solves your query, plz close the thread by rewarding each reply.

Regards

Former Member
0 Kudos
371

Hi,

Try this:

select <fld_list> into table itab
from but000 as a 
inner join but020 as b on a~partner = b~partner
inner join adrc as c on b~addrnumber = c~addrnumber.

0 Kudos
370

Thank you for your help on the issue !

While the structure of the sql is clear to me as the tables necessary are after i read your posts, i am concerned how the statement behaves in aspects of performance:

i am afraid that it will be too heavy to handle about 750 000 business partners.

any idea how i could make the whole thing faster or more efficient ?

0 Kudos
370

Hi Clemens,

You can make your query more faster if you can give any of the value for filtering the records in <b><i>WHERE</i></b> clause of <b><i>SELECT</i></b> query.

e.g. You can give direct values for any of the fields from the three tables in the <i><b>WHERE</b></i> clause. If you don't have direct value, a range will also help a lot in performance.

Best performance comes with the primary keys of the tables in join having direct values in <i><b>WHERE</b></i> clause.

Regards

0 Kudos
370

Hi clemens,

As per my understanding, u can further improve this SQL By:

1. first select only BU000 and BU020 with inner join and then select material details in different different itabs. and process This will surely reduce load on DB and may increase load on ABAP which u can improve by using BINARY SERARCH!.

(Important: if u are using two table in join and giviing only PARTNER which is primary key field in both tables in WHERE clasuse ... this should be the only criteria in where clasue then i Dont think it will affect performance .... how many record may be there, this query will be BEST performer... I hope it will fetch 750000 records whithin few seconds)

2. Generally, U try to give more and more conditions in where clause on PRIMARY KEY fiedls only and not on not KEY fields...

3. In worst case, fetch data from three differnt tables into three different itabs and then process, this is best and last way to reduce load on DB and will increase load on ABAP which can be manages as above..

Still u want more info .. send codes to me.. We will try to make it more and more fast....

Jogdand M B

null

Message was edited by:

Jogdand M B

0 Kudos
370

Clemens,

It will be very helpful if you tell us what you are trying to achieve. Based on what your requirements are we can determine the best way to structure your query. If you would need to look at only a few of the 750,000 records you will have to determine the criteria for selection. If you need to download all the information from the table you have no choice. You can probably set up a batch job (SM36).

Former Member
0 Kudos
370

Hi clemens,

The best way to join 3 tables is by using joins. Specify the main table first and then specify the item table and other tables.. Remember to give as many where conditions as possible for fields in all tables that U use.

harishaginati
Explorer
0 Kudos
370

Hi Buddy,

best thing is go for parallel processing...,

for instance you have selection criteria 1-10 break it into 1-3/ 3-7/ 8-10 and run each block in diffrent work processes..., and finally collect them.., you can either go for forall entries/ innner join if its header level data..., else go for for all entries

should solve your problem...., reward me if helpfull,

Cheers,

Harish