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: 

re field order in INNER JOIN

Former Member
0 Kudos
486

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

5 REPLIES 5

Rui_Dantas
Active Contributor
0 Kudos
135

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

0 Kudos
135

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
Active Contributor
0 Kudos
135

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

0 Kudos
135

Thank you all for the replies..

Regards

Ananya

Former Member
0 Kudos
135

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 TAB2A TAB1B TAB1C TAB2D

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 TAB2A TAB1B TAB1C TAB2D

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 TAB1B TAB1C TAB2A TAB2D

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