Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
is between preferable to >= and =<,

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tensweat

New User


Joined: 09 Jan 2006
Posts: 4
Location: Chennai

PostPosted: Thu Oct 05, 2006 4:15 pm    Post subject: is between preferable to >= and =<,
Reply with quote

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?
Back to top
View user's profile Send private message

cooolguy

New User


Joined: 05 Sep 2006
Posts: 9

PostPosted: Thu Oct 05, 2006 7:54 pm    Post subject: Re: is between preferable to >= and =<,
Reply with quote

On Performance point of view BETWEEN is preffered than <=& >=
Back to top
View user's profile Send private message
tensweat

New User


Joined: 09 Jan 2006
Posts: 4
Location: Chennai

PostPosted: Wed Oct 11, 2006 10:15 am    Post subject:
Reply with quote

thanks coool guy - but do you have any explanations for this?
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Nov 01, 2006 6:12 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Nov 01, 2006 7:26 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:


Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us