MVSFORUMS.com

[bufbooth]

Under DB2, is one of the below faster than the other?

1) FIELD NOT IN (" ")

2) FIELD NOT = " "

3) (FIELD > " " OR FIELD < " ")

I was told in the past that number 3 was faster than number 1 and 2.
I think the reason that I was given was that by using the NOT command, all of the records would have to be checked in order to determine if the condition was true, whereas using the < > commands, the record reading would stop once the condition was found to be true.

Thanks...

[schintala]

These are three different conditions. We can't judge which one gives optimized result. By thumb rule it is true that using greater or lesser than symbols definitely gives more optimized than "between" clause. I would run explain on SQL statement to know better use of these expressions.

[hisabarish]

Exactly, the no.3 is faster than 1,2. Because db2 takes the binary comparison ( ie., Binary search logic ). But the numbers 1 and 2 are first search the result and compare it.

other wise use the query in the explain command.. u will see the better result in Plan_table.
_________________
Sabari
Madras