topic Re: SELECT with WHERE/JOIN: performance in Application Development and Automation Discussions

SELECT with WHERE/JOIN: performance

gabriele_mazza — Fri, 10 May 2019 08:13:34 GMT

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?

Re: SELECT with WHERE/JOIN: performance

former_member184158 — Fri, 10 May 2019 08:22:28 GMT

Hi Gabriele,

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

Re: SELECT with WHERE/JOIN: performance

Sandra_Rossi — Fri, 10 May 2019 08:37:51 GMT

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).

Re: SELECT with WHERE/JOIN: performance

DoanManhQuynh — Fri, 10 May 2019 10:54:25 GMT

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

Re: SELECT with WHERE/JOIN: performance

matt — Fri, 10 May 2019 11:37:18 GMT

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