Where clause in Joins

[Former Member]

I have a selection screen like:

Table Field

EKKO EKORG

EKKO EKGRP

EKKO LIFNR

ESSR EBELN

ESSR EBELP

ESSR LBLNE

ESSR FRGZU

ESSR ERDAT

ESSR LOEKZ

Due to further complexities in the program i am joining 3 tables EKKO, EKPO and ESSR together. I can write the select stmt in 2 ways. I want to understand which one is better in terms of performance and is there exactly a rule here that i should follow before i write the where clause in this join.

SELECT a_{ebeln b}ebelp a_{lifnr a}ekorg a_{ekgrp b}packno c~lblni

c_{lblne c}erdat c_{packno c}loekz c_{frgsx c}frgkl c~frgzu

INTO TABLE gt_test

FROM ekko AS a INNER JOIN ekpo AS b

ON a_{ebeln = b}ebeln

INNER JOIN essr AS c

ON a_{ebeln = c}ebeln

AND b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp

WHERE a~lifnr IN so_lifnr

AND a~ekorg IN so_ekorg

AND a~ekgrp IN so_ekgrp

AND c~lblne IN so_lblne

AND c~ebeln IN so_ebeln

AND c~ebelp IN so_ebelp

AND c~erdat IN so_erdat.

SELECT a_{ebeln b}ebelp a_{lifnr a}ekorg a_{ekgrp b}packno c~lblni

c_{lblne c}erdat c_{packno c}loekz c_{frgsx c}frgkl c~frgzu

INTO TABLE gt_test

FROM ekko AS a INNER JOIN ekpo AS b

ON a_{ebeln = b}ebeln

INNER JOIN essr AS c

ON a_{ebeln = c}ebeln

AND b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp

WHERE a~ebeln IN so_ebeln

AND a~lifnr IN so_lifnr

AND a~ekorg IN so_ekorg

AND a~ekgrp IN so_ekgrp

AND b~ebelp IN so_ebelp

AND c~lblne IN so_lblne

AND c~erdat IN so_erdat.

AND c~erdat IN so_erdat.

[Former Member]

Hi,

This part is not necessary:

... a~ebeln = c~ebeln ...

Modify:

AND c~ebeln IN so_ebeln
AND c~ebelp IN so_ebelp

by

AND a~ebeln IN so_ebeln
AND b~ebelp IN so_ebelp

The best option will be:

if not so_ebeln[] is initial.
   select ... where a~ebeln IN so_ebeln ... (2nd select)
else.
   select ... where a~lifnr IN so_lifnr ... (1st select)
endif.

Also, you can try only the 2nd select, because it has WHERE clause to ebeln and lifnr. Depending on your database, you can check (via ST04) if the right index is selected (eg. if you inform so_ebeln the database must do the selection using ebeln index. if you inform so_lifnr the database must do the selection using lifnr index, etc..)

Best regards,

Leandro Mengue

[Former Member]

Hi Leandro,

Thanks for the useful suggestion. I wanted to understand a few things here. Is this part going to add any overhead in performance.

... a~ebeln = c~ebeln ...

Now that i see that the below one is not recommended and they are also not the key fields in table ESSR.

AND c~ebeln IN so_ebeln
AND c~ebelp IN so_ebelp

And as you have suggested to use

AND a~ebeln IN so_ebeln
AND b~ebelp IN so_ebelp

Is this something like we should always go for key fields comparison in where clause. Here EKKO-EBELN and EKPO-EBELP form a part of key fields in their table, while it can be seen that selection screen picks PO from ESSR

ESSR EBELN

ESSR EBELP.

I just had this thought that comparing PO order and line item from ESSR will bring down the selection to very less numbers as ESSR mostly contains data in thousand while that in EKPO or EKKO is always very large.

Regards,

Rakesh

[Former Member]

Hi Rakesh,

If your ESSR table has few record than EKKO / EKPO, you can try invert the selection, like: ...FROM ESSR ... JOIN EKPO ... JOIN EKKO...

The goal of this is to put as principal table (FROM xxxx) the table that will have less records (because if a record in the principal table does not match the selection, the database will not search for the rest of join for this record).

If some query has a performance problem, is very important to analyse the method of selection used by the database (full scan, index scan, index used, etc.). (via ST04 , ST05, etc.)

Some times the database (eg. Oracle) select a non usefull index to do your selection. See this example:

table has only one index by field1.

SELECT ... FROM table WHERE field1 in s_field1 and field2 in s_field2.

If in s_field1 you have a large range (like 0000000 to 99999999) and s_field2 is restrictive (like =XX), probably the database will use the index to search, but, in this case, a full scan without index is better, beause look the entire table using an index is more slow that look to the entire table wihout index.

Of course, indexes are very good if you have apropriatedes values to find.

Best regards,

Leandro Mengue

[Former Member]

Hi,

Select all required field from EKKO and fill an internal table and then select data from ESSR table for all entries in EKKO table .

and further fetch data by read table .

This way you can surely improve performance.

--

Ashutosh

[Former Member]

Hi,

Sorry Ashutosh, I disagree from you. Move records from database to memory, process it, re-select for each one, process again, re-select again, etc, will not be faster. This procedure moves data across system (database / applcation server) too much times.

Best regards,

Leandro Mengue