which Inner join will give better performance?

[former_member268961]

Hi,

Here i posted two queries one with nested inner join or flat.

out of this two quries which one will give better performance?

<b>1)flat iiner join:</b>

select

a~col1

b~col2

c~col3

from ( table1 as a

INNER JOIN table2 as b on b_{col2 = a}col1

INNER JOIN tabke3 as c on c_{col3 = a}col1 )

into corresponding fields of table lt_tab

where

a~col11 = 'AB' AND

b~col22 = 'CD' AND

c~col33 = 'EF'.

<b>2) with nested inner join:</b>

select

a~col1

b~col2

c~col3

from ( ( table1 as a

INNER JOIN table2 as b on b_{col2 = a}col1 )

INNER JOIN tabke3 as c on c_{col3 = a}col1 )

into corresponding fields of table lt_tab

where

a~col11 = 'AB' AND

b~col22 = 'CD' AND

c~col33 = 'EF'.

Note: Table1 is index using col11 ( 200,000 enteries )

Table2 ( 50,000 enteries )

Table3 is index using col3 ( 200,000 enteries )

Please explain the reason?

Regards,

Prakash.

[Former Member]

Hi Prakash,

I hope both consume same time to execute. But flat inner join will be fast rather than nested inner join. But it is negligable.

If you are using more than 2 table in joins, better to create a view and retrieve the data. It will give effective results.

Regards

Bhupal Reddy

[former_member268961]

Hi Bhupal Reddy,

why are you saying the first will be faster?can you explain bit more???

Regards,

Prakash.

[Former Member]

Hi Prakash,

Use the statement GET RUN TIME - and determine execution time for these two queries. My opinion is the first one little bit fast rather than second. It is almost same.

Regards

Bhupal Reddy

[Former Member]

hi

good

both of the inner join statement are same and the code that you have written ll take the appropriate time to access the data from the database,

thanks

mrutyun^

[former_member268961]

Hello

can you explain why this query will take more time?

Regards,

Prakash

[Former Member]

hi,

Put eacgh query in separate program along with exact table name and run in se30 and check the performance .youu will find which one is giving better performance after analysis them.

Debjani