SELECT with WHERE/JOIN: performance

[gabriele_mazza]

Hi experts, I have a question on JOIN condition. Sometimes i need to insert the same fields in WHERE condition and in JOIN condition.

For example:

  SELECT        * 
         FROM kblk join kblp
              ON kblk~belnr eq kblp~belnr
         WHERE  kblk~belnr  eq lv_belnr
           AND  kblp~blpos  eq lv_blpos. 

My question is about performances of this select. What is processed before? Where or join?

If WHERE condition is processed before

JOIN
  SELECT        * 
         FROM  kblk join kblp
              ON kblk~belnr eq kblp~belnr
         WHERE  kblk~belnr  eq lv_belnr
           AND  kblp~belnr  eq lv_belnr
    AND  kblp~blpos  eq lv_blpos. 

is better because i can express all the key fields of table KBLP.

if JOIN condition is processed before WHERE the condition "kblp~belnr eq lv_belnr" is useless because of "kblk~belnr eq lv_belnr". The joined records already have the filter on belnr.

What is the best condition in performance?

[gabriele_mazza]

Hi experts, I have a question on JOIN condition. Sometimes i need to insert the same fields in WHERE condition and in JOIN condition.

For example:

  SELECT        * 
         FROM kblk join kblp
              ON kblk~belnr eq kblp~belnr
         WHERE  kblk~belnr  eq lv_belnr
           AND  kblp~blpos  eq lv_blpos. 

My question is about performances of this select. What is processed before? Where or join?

If WHERE condition is processed before

JOIN
  SELECT        * 
         FROM  kblk join kblp
              ON kblk~belnr eq kblp~belnr
         WHERE  kblk~belnr  eq lv_belnr
           AND  kblp~belnr  eq lv_belnr
    AND  kblp~blpos  eq lv_blpos. 

is better because i can express all the key fields of table KBLP.

if JOIN condition is processed before WHERE the condition "kblp~belnr eq lv_belnr" is useless because of "kblk~belnr eq lv_belnr". The joined records already have the filter on belnr.

What is the best condition in performance?

[former_member184158]

Hi Gabriele,

coul you try ST05 to analyze the performance and compare between them which on is faster.

[Sandra_Rossi]

In complement to what said Ebrahim Hatem, in ST05 you have the possibility to see the execution plan, which tells you what the database decides in your conditions (might differ on other systems).

[DoanManhQuynh]

put F1 help on WHERE, i can see that its said:

The addition WHERE restricts the number of lines included in the result set by the statement SELECT...

so I think JOIN will evaluate first. But there are some discussions about it:

https://dba.stackexchange.com/questions/5038/sql-server-join-where-processing-order

[matt]

I did once get it the wrong way round and ended up running out of memory.