Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Which is more efficient?

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

New User


Joined: 14 Jun 2012
Posts: 70
Location: India

PostPosted: Wed Mar 16, 2016 2:55 pm    Post subject: Which is more efficient?
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7224

PostPosted: Wed Mar 16, 2016 3:12 pm    Post subject: Reply to: Which is more efficient?
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: 70
Location: India

PostPosted: Wed Mar 16, 2016 3:22 pm    Post subject:
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: 70
Location: India

PostPosted: Wed Mar 16, 2016 3:27 pm    Post subject:
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    Post subject:
Reply with quote

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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Mar 17, 2016 10:01 pm    Post subject:
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: 70
Location: India

PostPosted: Fri Mar 18, 2016 12:01 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am
No new posts Choosing efficient computational data... sachinji84 COBOL Programming 1 Fri Feb 13, 2015 8:39 am
No new posts Please suggest efficient SQL query fo... kunal jain DB2 18 Wed Jul 31, 2013 5:35 pm
No new posts efficient table copy in COBOL Bill Woodger COBOL Programming 3 Sun May 26, 2013 4:32 am
No new posts Joinkeys - Efficient solution for unp... senjay DFSORT/ICETOOL 3 Thu Sep 06, 2012 11:15 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us