Difference between using IN and an inner OR

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

Hi

<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

[Former Member]

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

[Former Member]

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.