Use of inequality in where clause

[Justin]

We have been testing a few sql statements and noticed that using an
inequality, for example:
where recdate '08/25/2008'
has much poorer performance than
where recdate between '08/25/2008' and '10/1/2008'

We saw timings of 5 1/2 minutes vs 1 second.

It also appears (from explain) that both statements use the index. Is
there documentation that someone could reference that would direct one
toward why the db engine is so much slower with the first statement?

[Mark A]

Re: Use of inequality in where clause

"Justin" <kfwolf@hotmail .comwrote in message
news:deb3743f-2b76-413c-864a-1dcc7a0d5490@m3 6g2000hse.googl egroups.com...

We have been testing a few sql statements and noticed that using an
inequality, for example:
where recdate '08/25/2008'
has much poorer performance than
where recdate between '08/25/2008' and '10/1/2008'
>
We saw timings of 5 1/2 minutes vs 1 second.
>
It also appears (from explain) that both statements use the index. Is
there documentation that someone could reference that would direct one
toward why the db engine is so much slower with the first statement?

They both may read the index, but one may scan the entire index whereas the
other may use the b-tree. Also, one may use the index to filter the rows,
while another may only use the index in a join (since you did not post the
exact SQL, it is hard to know).

Are you using dynamic SQL with the literals in the statement, or do you have
host variables. If you have host variables and/or no distribution stats,
then DB2 assumes that more rows will be retrieved with a or than with
between.

I would make sure an do runstats with the following (assuming DB2 for LUW):
runstats on table table-name with distribution on key columns and indexes
all

[Justin]

Re: Use of inequality in where clause


Mark,

Thanks that was helpful. Here is the exact query. There are no host
variables:

select survey_id from userid.survey where client_id = 311 and
added_date ‘8/25/2008’


On Sep 17, 6:07 pm, "Mark A" <some...@someon e.comwrote:

"Justin" <kfw...@hotmail .comwrote in message
>
news:deb3743f-2b76-413c-864a-1dcc7a0d5490@m3 6g2000hse.googl egroups.com...
>

We have been testing a few sql statements and noticed that using an
inequality, for example:
where recdate '08/25/2008'
has much poorer performance than
where recdate between '08/25/2008' and '10/1/2008'

>

We saw timings of 5 1/2 minutes vs 1 second.

>

It also appears (from explain) that both statements use the index.  Is
there documentation that someone could reference that would direct one
toward why the db engine is so much slower with the first statement?

>
They both may read the index, but one may scan the entire index whereas the
other may use the b-tree. Also, one may use the index to filter the rows,
while another may only use the index in a join (since you did not post the
exact SQL, it is hard to know).
>
Are you using dynamic SQL with the literals in the statement, or do you have
host variables. If you have host variables and/or no distribution stats,
then DB2 assumes that more rows will be retrieved with a or than with
between.
>
I would make sure an do runstats with the following (assuming DB2 for LUW):
runstats on table table-name with distribution on key columns and indexes
all