is between preferable to >= and =<,

tensweat · New User Joined: 09 Jan 2006 Posts: 4 Location: Chennai

Is BETWEEN preferable to >= and =<, in the latest versions of DB2 UDB - that are later to v6? response, sincerely awaited.

I have checked for any migration guide from v5 to v6, but there is nothing as such. So, can you suggest where to find this?

cooolguy · New User Joined: 05 Sep 2006 Posts: 9

On Performance point of view BETWEEN is preffered than <=& >=

tensweat · New User Joined: 09 Jan 2006 Posts: 4 Location: Chennai

thanks coool guy - but do you have any explanations for this?

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

If the optimizer knows that a search covers a range it will likely choose a nonmatching index scan. Between is more efficient than using IN.

The following three queries will return the same results, but the query using BETWEEN is more efficient than using IN, or a combination of >=, ,<=

EXEC SQL
SELECT SN, PN, JN
FROM SPJ
WHERE QTY BETWEEN 100 and 300
END-EXEC

EXEC SQL
SELECT SN, PN, JN
FROM SPJ
WHERE QTY IN(100,200,300)
END-EXEC

EXEC SQL
SELECT SN, PN, JN
FROM SPJ
WHERE QTY >=100 and <= 300)
END-EXEC

yoyoyo

DavidatK · Posted: Wed Nov 01, 2006 7:26 pm

The use of ?BEWTEEN? and <= and >= is usually a wash in performance. However, there is one condition where it is not a wash.

If, for conversation, we say that you have a table where part of the KEY is EFF_DATE and EXPIRE_DATE. And you are looking for rows that are currently active. The are two ways to select them.

SELECT *
FROM TABLE
WHERE EFF_DATE <= :CURRENT-DATE
AND EXPIRE_DATE >= :CURRENT-DATE

Or

SELECT *
FROM TABLE
WHERE :CURRENT-DATE BETWEEN EFF_DATE AND EXPIRE_DATE

In the first case DB2 will use the index to select the rows, good performance,
But in the second case, DB2 cannot use the indexes to select the rows, and must read every row in the table to select the rows. This really becomes an issue if you also add to the SQL

?AND ACCOUNT_TYPE = :WS-ACCOUNT-TYPE? and you have to read the entire table for each account type you want to process.

Dave