topic Re: Difference between using IN and an inner OR in Application Development and Automation Discussions

Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 03:49:19 GMT

Hi,

I need to know what's the difference between using IN and an inner OR in SQL statements.

I need to know their affect on the load of database server and the application server.

Hope someone can help me. Thanks.

For using Inner OR:

SELECT *

FROM SFLIGHT

INTO SFLIGHT_WA

WHERE CARRID = 'LH'

AND ( CONNID = '0300' OR

CONNID = '0302' )

AND FLDATE LIKE '2003%'.

ENDSELECT.

For using IN operator:

SELECT *

FROM SFLIGHT

INTO SFLIGHT_WA

WHERE CARRID = 'LH'

AND CONNID IN ('0300', '0302')

AND FLDATE LIKE '2003%'.

ENDSELECT.

Re: Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 05:42:25 GMT

Hi ,

Using IN will enhance the speed as the evaluation of the logical expression is faster .

But when many ORs are joined , the evaluation is not as fast and the query takes more time .

Thanks

Re: Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 05:49:12 GMT

I think if you are using IN there will be an addtional process of conversion of OPEN sql to Native SQL. While if you use inner-OR, it will be directly executed as native SQL.

'IN' : additional load on Apps server

'OR': no additional load on Apps server

I am not very sure if IN will improve the performance.

Experts....Please correct me if I am wrong

Regards,

Vishal

Re: Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 07:58:37 GMT

<u>Using OR</u>

The optimizer usually stops working when an 'OR expression' occurs in the condition. This means that the columns checked using OR are not included in the index search. An exception to this are 'OR expressions' at the outside of conditions.

You should try to reformulate conditions that apply 'OR expressions' to columns relevant to the index into an IN condition.

Regards,

Madhu

Re: Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 08:30:36 GMT

The performance of using IN operater is faster than using inner OR.

Any other idea of what is the difference between this two?

Thanks for reply

Re: Difference between using IN and an inner OR

Former Member — Wed, 25 Apr 2007 10:10:19 GMT

If you do runtime analysis for each select statement, both are taking ABAP excecution time more or less same only 2 % variation is there

database excecution time less if you use IN when compare to OR operator.

There is 9% variation.

So definitely IN is more efficient than OR.