Which is better? "BETWEEN" vs "<=" and >"="

Sheldon Rich

Which is better? "BETWEEN" vs "<=" and >"="
As part of our DB2 performance review another interesting question has
developed. Which is the most efficient way to retrieve rows that are between
two values.
Method 1:
Select * from mytab
where cola between :hv1 and :hv2

Method 2:
Select * from mytab
where cola >= :hv1
and cola <= :hv2

Two possibilities exist: value of cola available in index or table data will be
needed to determine value of cola. Is there a difference?

Which is the better and most efficient way to retrieve the data? Are there
other methods to retrieve these rows?

Thanks to all,

Sheldon Rich
Bank Tfahot
[login to unmask email]



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: Which is better? "BETWEEN" vs "<=" and >"="
(in response to Sheldon Rich)
I know Terry is out there somewhere, but I'm kind of hoping that in the 21st
century DB2 is smart enough to know these are equivalent and would rewrite
one into the other or at least treat them the same

Now I'll wait for someone to patiently tell me circumstances when they are
NOT equivalent statements!

Phil G
Grainger Database Solutions

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sheldon Rich
Sent: 14 January 2010 13:41
To: [login to unmask email]
Subject: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="

As part of our DB2 performance review another interesting question has
developed. Which is the most efficient way to retrieve rows that are
between
two values.
Method 1:
Select * from mytab
where cola between :hv1 and :hv2

Method 2:
Select * from mytab
where cola >= :hv1
and cola <= :hv2

Two possibilities exist: value of cola available in index or table data
will be
needed to determine value of cola. Is there a difference?

Which is the better and most efficient way to retrieve the data? Are there
other methods to retrieve these rows?

Thanks to all,

Sheldon Rich
Bank Tfahot
[login to unmask email]



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roger Hecq

Re: Which is better? "BETWEEN" vs "<=" and >"="
(in response to Phil Grainger)
As usual, there is no absolute answer. I am sure that there are
exceptions, but my experience is that the Between with consistently
outperform 2 range predicates. This is particularly true if the
predicate is a matching column predicate on the index being used to
access the table. The difference is not as important if the predicate
is applied in an index screen or on a non-indexed predicate. My belief
is that the Between predicate has been the recommended approach since it
was introduced. The best approach would be to use Explain or Visual
Explain (or its successors) to compare the projected cost of the 2
formulations.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sheldon Rich
Sent: Thursday, January 14, 2010 8:41 AM
To: [login to unmask email]
Subject: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="

As part of our DB2 performance review another interesting question has
developed. Which is the most efficient way to retrieve rows that are
between two values.
Method 1:
Select * from mytab
where cola between :hv1 and :hv2

Method 2:
Select * from mytab
where cola >= :hv1
and cola <= :hv2

Two possibilities exist: value of cola available in index or table data
will be needed to determine value of cola. Is there a difference?

Which is the better and most efficient way to retrieve the data? Are
there other methods to retrieve these rows?

Thanks to all,

Sheldon Rich
Bank Tfahot
[login to unmask email]



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Cuneyt Goksu

Re: Which is better? "BETWEEN" vs "<=" and >"="
(in response to Roger Hecq)
Hi,

If you have an index on cola or a composite index starting with cola,
both queries will be indexable, Matchcol = 1 and stage 1.

Regards,
Cuneyt

> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
> Sheldon Rich
> Sent: Thursday, January 14, 2010 3:41 PM
> To: [login to unmask email]
> Subject: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="
>
>
> As part of our DB2 performance review another interesting
> question has
> developed. Which is the most efficient way to retrieve rows
> that are between
> two values.
> Method 1:
> Select * from mytab
> where cola between :hv1 and :hv2
>
> Method 2:
> Select * from mytab
> where cola >= :hv1
> and cola <= :hv2
>
> Two possibilities exist: value of cola available in index or
> table data will be
> needed to determine value of cola. Is there a difference?
>
> Which is the better and most efficient way to retrieve the
> data? Are there
> other methods to retrieve these rows?
>
> Thanks to all,
>
> Sheldon Rich
> Bank Tfahot
> [login to unmask email]
>
>
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation,
Tuning, Coding, BI, Warehouses, - among many more categories of help
waiting for you! Whether you are an old hand or a DB2 newbie, we have
presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Arnold

Re: Which is better? "BETWEEN" vs "<=" and >"="
(in response to Cuneyt Goksu)
An additional question, perhaps out of scope for your purposes, is which option is clearer for the folks writing and maintaining the code. If you are developing coding standards, that's an important question...

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Thursday, January 14, 2010 9:02 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="

I know Terry is out there somewhere, but I'm kind of hoping that in the 21st
century DB2 is smart enough to know these are equivalent and would rewrite
one into the other or at least treat them the same

Now I'll wait for someone to patiently tell me circumstances when they are
NOT equivalent statements!

Phil G
Grainger Database Solutions

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sheldon Rich
Sent: 14 January 2010 13:41
To: [login to unmask email]
Subject: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="

As part of our DB2 performance review another interesting question has
developed. Which is the most efficient way to retrieve rows that are
between
two values.
Method 1:
Select * from mytab
where cola between :hv1 and :hv2

Method 2:
Select * from mytab
where cola >= :hv1
and cola <= :hv2

Two possibilities exist: value of cola available in index or table data
will be
needed to determine value of cola. Is there a difference?

Which is the better and most efficient way to retrieve the data? Are there
other methods to retrieve these rows?

Thanks to all,

Sheldon Rich
Bank Tfahot
[login to unmask email]



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L