topic Re: which Inner join will give better performance? in Application Development and Automation Discussions

which Inner join will give better performance?

former_member268961 — Fri, 15 Dec 2006 09:00:41 GMT

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.

Re: which Inner join will give better performance?

Former Member — Fri, 15 Dec 2006 09:14:26 GMT

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

Re: which Inner join will give better performance?

former_member268961 — Fri, 15 Dec 2006 09:17:59 GMT

Hi Bhupal Reddy,

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

Regards,

Prakash.

Re: which Inner join will give better performance?

Former Member — Fri, 15 Dec 2006 09:18:58 GMT

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^

Re: which Inner join will give better performance?

former_member268961 — Fri, 15 Dec 2006 09:21:12 GMT

Hello

can you explain why this query will take more time?

Regards,

Prakash

Re: which Inner join will give better performance?

Former Member — Fri, 15 Dec 2006 09:21:48 GMT

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

Re: which Inner join will give better performance?

Former Member — Fri, 15 Dec 2006 09:25:49 GMT

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