IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Which is more efficient?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
pshongal

New User


Joined: 14 Jun 2012
Posts: 96
Location: India

PostPosted: Wed Mar 16, 2016 2:55 pm
Reply with quote

Hi,

I have a query as below. Which is more efficient? using BETWEEN or '<=, >='

Code:

SELECT field                                     
  FROM table1                       
 WHERE fld1 = 'abc'                         
   AND fld2 IN ('aaa','bbb')       
   AND CREAT_TS  BETWEEN                           
    '2016-03-14-13.43.04.577117'          AND     
    '2016-03-14-13.43.33.882498'                   
WITH UR;         

SELECT field                                     
  FROM table1                       
 WHERE fld1 = 'abc'                         
   AND fld2 IN ('aaa','bbb')       
   AND CREAT_TS  >='2016-03-14-00.00.00.000000'
   AND CREAT_TS  <='2016-03-15-00.00.00.000000'                   
WITH UR;                                   
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Mar 16, 2016 3:12 pm
Reply with quote

Well, what are your thoughts, and your references to back those up?

You realise you have different values on your conditions, so even if you knew the "general" answer, the specific case with your data could not be answered. You may get 200m results from one, and only seven from the other, if your data happened to be like that.
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 96
Location: India

PostPosted: Wed Mar 16, 2016 3:22 pm
Reply with quote

Hi Bill,

Sorry, value in query is same, it was typo.
My thought was BETWEEN is not as efficient as other one.

Code:

AND CREAT_TS  BETWEEN                           
    '2016-03-14-00.00.00.000000'          AND     
    '2016-03-15-00.00.00.000000'               

AND CREAT_TS  >='2016-03-14-00.00.00.000000'
   AND CREAT_TS  <='2016-03-15-00.00.00.000000'       


Expected O/P from the query is around 5MM and it runs daily.[/code]
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 96
Location: India

PostPosted: Wed Mar 16, 2016 3:27 pm
Reply with quote

One more thing is, CREAT_TS is also part of index
Back to top
View user's profile Send private message
Awanti

New User


Joined: 11 Mar 2016
Posts: 4
Location: India

PostPosted: Wed Mar 16, 2016 5:03 pm
Reply with quote

I think in your case BETWEEN is better
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Mar 17, 2016 10:01 pm
Reply with quote

I always found BETWEEN takes more time than <= as per the MIPS of our many Stored Procedures and DBA. However, you could run a parallel tests in the region and let you DBA gives you SQL stats and then you make your decision.

Code:
SQL provides two ways to request a search for values that fall within a range (range predicates): by using the relational predicates >= and <= or by using BETWEEN. These predicates are functionally equivalent but may perform differently.

The optimizer uses default filter factors when determining the access path for range predicates. A filter factor (FF) is an estimate the optimizer uses to estimate how many rows will qualify for the predicate. Default FFs are used for range predicates because the search values are not known until execution time when they are provided by the user and can vary from one execution of the program to another. The default FF is lower for BETWEEN than for other range predicates.

For example, compare the following two statements:
SELECT SN, PN, JN, QTY
FROM   SPJ
WHERE  PN >= 'P2';

SELECT SN, PN, JN, QTY
FROM   SPJ
WHERE  PN BETWEEN 'P2' AND 'ZZZZZZ';

The optimizer can choose to use an index on PN for the search in either case, but it is more likely to use an index for the statement using BETWEEN, particularly when using host variables. This is because the optimizer uses default FFs unless reoptimization is specified when binding a plan or package. Use the highest possible value for a column, 'ZZZZZZ' as in the example. Do not use X'00' and X'FF' as the low and high values because they are treated as the same value. They are outside the range of A-Z and 0-9, and blank considered in filter factor calculations which makes the use of indexes likely even though it may not be best.

The optimizer automatically transforms >= AND <= predicates to a BETWEEN predicate. For example, the WHERE clause:
WHERE  PN >= 'P2'
AND    PN <= 'P9'

is transformed to:
WHERE PN BETWEEN 'P2' AND 'P9'
Back to top
View user's profile Send private message
pshongal

New User


Joined: 14 Jun 2012
Posts: 96
Location: India

PostPosted: Fri Mar 18, 2016 12:01 pm
Reply with quote

Thanks for the input guys.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How efficient are you, while working ... General Talk & Fun Stuff 0
No new posts SMS:- Efficient filter lists in ACS All Other Mainframe Topics 7
No new posts Efficient sorting DFSORT/ICETOOL 5
No new posts Choosing efficient computational data... COBOL Programming 1
No new posts Please suggest efficient SQL query fo... DB2 18
Search our Forums:

Back to Top