re field order in INNER JOIN

[Former Member]

Hi,

I need a suggestion for improvement of my inner JOIN statement.

internal table itab is of below type

type: begin of ty_itab

field a

field b

field c

field d

end of ty_itab

I have to join two database table TABLE1 and TABLE2. Fields a and d are from TABLE2 and fields b and c are from TABLE 1.

But in the internal table I must have the fields in the order fielda,b,c,d.

Which of the below two options will be better for performance ? And why ?

option 1:

SELECT tab2~a

tab1~b

tab1~c

tab2~d into corresponding fields of itab from TABLE1 as tab1 and TABLE2 as tab2 on..........

option2:

SELECT tab1~b

tab1~c

tab2~a

tab2~d into corresponding fields of itab from TABLE1 as tab1 and TABLE2 as tab2 on..........

Thanks in advance...

Best Regards

Ananya Mukherjee

Moderator message - Cross post locked

Edited by: Rob Burbank on May 28, 2009 9:43 AM

[Rui_Dantas]

Hello,

I am not sure I understand your problem. What makes you think those two options are any different?

Anyway, what you problem want is specify the fields in the same sequence as the internal table and do SELECT INTO TABLE itab.

Hope this helps,

Rui Dantas

[Former Member]

Hi Rui,

I want to know whether selecting field 'a' from TABLE2 and then fields b and c from TABLE1 and again field d from TABLE2 hampers the performance in any manner.

Or is it better to select fields of same table first and then shift to the other table ?

Thanks&Regards

Ananya

[former_member194613]

first of all INTO CORRESPONDING is no performance issue, maybe it was a long time ago.

You will have hard problems to figure out differences between your 2 variants, because the

general variation of SELECT statements is larger than the INTO CORRESPONDING costs.

But still, if you can use INTO TABLE then use it. Use it INTO CORRESPONDING if the result

structure or table is determined by other influences.

Better invest your time in learning more about joins, and the issues about joins are how data are

found, not how already found data are transferred.

Siegfried

[Former Member]

Thank you all for the replies..

Regards

Ananya

[Former Member]

I am not satisfied with your two options. Because of following reason,

1. First I want to know which one is master table and item table. Because in inner join select query you want to use master table as first and you want to use the item table as second.

Example:

SELECT TAB2_{A TAB1}B TAB1_{C TAB2}D

INTO TABLE ITAB

FROM <MASTER TABLE1> AS TAB1 INNER JOIN <ITEM TABLE2> AS TAB2 ON..........

2. You should not use the INTO CORRESPONDING FIELDS OF ITAB. Instead of this you can change the field order in internal table or in select query.

Example:

Use following way

TYPE: BEGIN OF TY_ITAB

FIELD A

FIELD B

FIELD C

FIELD D

END OF TY_ITAB

SELECT TAB2_{A TAB1}B TAB1_{C TAB2}D

INTO TABLE ITAB

FROM <MASTER TABLE1> AS TAB1 INNER JOIN <ITEM TABLE2> AS TAB2 ON..........

Or , Anyway you are going to use in program only. It will not affect your logic if change the field order in the internal table.

TYPE: BEGIN OF TY_ITAB

FIELD B

FIELD C

FIELD A

FIELD D

END OF TY_ITAB

SELECT TAB1_{B TAB1}C TAB2_{A TAB2}D

INTO TABLE ITAB

FROM <MASTER TABLE1> AS TAB1 INNER JOIN <ITEM TABLE2> AS TAB2 ON..........

3. In inner join query, the order of Where condition also create the perform issue. Because in where condition maximum you try to use all the primary key from <MASTER TABLE1> and <ITEM TABLE2> and field order in where condition should be same order in <MASTER TABLE1> and <ITEM TABLE>.

This are the things you want to take care when you try to use INNER JOIN Query. If you have any clarification please let me know. I will try answer your Questions.

-

---

Ravi.

Edited by: RavivarmanP on May 28, 2009 6:55 PM