Best way to JOIN 3 tables into internal table ?

[former_member5350]

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

[Former Member]

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 BUT000_{PARTNER = BUT020}PARTNER

INNER JOIN ADRC ON ADRC_{ADDRNUMBER = BUT020}ADDRNUMBER.

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]

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.

[former_member5350]

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 ?

[Former Member]

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

[Former Member]

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

[Former Member]

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]

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]

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