# Which is more efficient?

Author Message
pshongal

New User

Joined: 14 Jun 2012
Posts: 96
Location: India

Posted: Wed Mar 16, 2016 2:55 pm

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;
Bill Woodger

Moderator Emeritus

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

 Posted: Wed Mar 16, 2016 3:12 pm 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.
pshongal

New User

Joined: 14 Jun 2012
Posts: 96
Location: India

Posted: Wed Mar 16, 2016 3:22 pm

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]
pshongal

New User

Joined: 14 Jun 2012
Posts: 96
Location: India

 Posted: Wed Mar 16, 2016 3:27 pm One more thing is, CREAT_TS is also part of index
Awanti

New User

Joined: 11 Mar 2016
Posts: 4
Location: India

 Posted: Wed Mar 16, 2016 5:03 pm I think in your case BETWEEN is better
Rohit Umarjikar

Global Moderator

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

Posted: Thu Mar 17, 2016 10:01 pm

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'
pshongal

New User

Joined: 14 Jun 2012
Posts: 96
Location: India

 Posted: Fri Mar 18, 2016 12:01 pm Thanks for the input guys.
 View Bookmarks All times are GMT + 6 Hours

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

 © 2003-2021  IBM MAINFRAMES Software & Support Division Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us