Better performance while joining tables

[Former Member]

Dear friends

When we join 2 tables, say EKKO & EKPO in select query what is the order of fields and the order of where conditions to be given for getting better performance.

Is it all fields from header table first and then from item table??

Regards

Sathar

[Former Member]

Hi,

First is the header table and then the line item table.

All the fields in the where cond. should be in the same order of the primary keys .

If you give all the necessary fields in the select stmt in the same order as in the table , it will be fast. Better not to give into corresponding fields of table.

Avoid select *.

Regards,

Subramanian

[nikhil_chitre]

Hi,

For better performance use syntax For All Entries.

First select data from EKKO then from EKPO for all entries in Itab 1.

Regards,

[Former Member]

Hi Sathar,

if you join two tables in SQL, there is no prefered order in fields or in predicates in the WHERE clause. There is also no need for an order in the FROM clause. This is the theory and the supported database getting better and better in realizing this.

If we now look to your example EKKO & EKPO, usually you'll search for some header data first and want to select the line items in addition. But may be you have a more restrictive predicate on the line item table and there is a suitable index on it, then the database optimizer would use this table first and then read the header lines for the line items found. What I would like to tell you is, all depends on the WHERE clause. Which table the predicates in WHERE clause filters less lines, the more likely it will be used as driving table (this is the table read first by the database). In your example it is very likely that EKKO will be the driving table because there is at least one line in EKKO for each line in EKPO but more the other way around.

For best performance you should choose the join condition wright:

FROM EKKO INNER JOIN EKPO

ON EKKO_{EBELN = EKPO}EBELN

AND EKKO_{MANDT = EKPO}MANDT (if you want to be very friendly to the optimizer)

Then choose only the fields you really need in the selection list and specify the where clause to select least possible number of rows. You could check if there is a suitable index for performance.

Best regards

Ralph

[JozsefSzikszai]

There are some wrong informations here, which need to be corrected:

"First is the header table and then the line item table."

==> It does not matter

"All the fields in the where cond. should be in the same order of the primary keys ."

==> This is again not true...

"For better performance use syntax For All Entries."

==> I've read this here on SDN 1000th times, but it is just simply not true or at least not that simple. You always have to give a try, but I believe that in this case (EKKO and EKPO) the JOIN will be faster.

"For best performance you should choose the join condition wright:

FROM EKKO INNER JOIN EKPO

ON EKKO_{EBELN = EKPO}EBELN

AND EKKO_{MANDT = EKPO}MANDT (if you want to be very friendly to the optimizer)"

==> The client field (MANDT) can be forgotten, you don't have to specify at all.

"Then choose only the fields you really need in the selection list and specify the where clause to select least possible number of rows. You could check if there is a suitable index for performance."

==> No index will be helpful by JOINs

hope this helps

ec

[Former Member]

Hi Eric,

good points at the beginning, but your statements about my posting requires an answer.

I wrote that the client filed is not necessary, but in some strange situations the optimizer has chosen the wrong path because it does not get this additional information. This happens probably not in such an easy join but it happend on one of my systems. But in principle you're wright, it is not necessary.

Your other statement is wrong. Indexes matter for joins. You can verify this easily be checking the execution plan. If you mean secondary indexes my the term index, then you're wright, because in the example the primary key index will be used.

Regards

Ralph