By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,975 Members | 1,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,975 IT Pros & Developers. It's quick & easy.

Improve query time

P: n/a
I have a table containing 5.1 million rows. The DDL of the table is
shown below (only relevant parts shown).

CREATE TABLE XXX.XXXX (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE DATE NOT NULL,
ENDTIME TIME NOT NULL,
...
...
PRIMARY KEY (ENDDATE,ENDTIME,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like this:

select *
from XXX.XXXX
where enddate='2003-11-05'

The query executes very fast (less than 1 second). But when I do a
query like this:

select *
from XXX.XXXX
where timestamp(enddate,endtime) between
timestamp('2003-11-05','07.00.00') and
timestamp('2003-11-05','15.00.00')

The query takes a few minutes.

Beside deleting the earlier records in the table, how else can I
improve the speed of the second query? What kind of index should I
create for the table?

Thanks in advance.

Yong Sing
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59**************************@posting.google.c om...
I have a table containing 5.1 million rows. The DDL of the table is
shown below (only relevant parts shown).

CREATE TABLE XXX.XXXX (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE DATE NOT NULL,
ENDTIME TIME NOT NULL,
...
...
PRIMARY KEY (ENDDATE,ENDTIME,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like this:

select *
from XXX.XXXX
where enddate='2003-11-05'

The query executes very fast (less than 1 second). But when I do a
query like this:

select *
from XXX.XXXX
where timestamp(enddate,endtime) between
timestamp('2003-11-05','07.00.00') and
timestamp('2003-11-05','15.00.00')

The query takes a few minutes.

Beside deleting the earlier records in the table, how else can I
improve the speed of the second query? What kind of index should I
create for the table?

Thanks in advance.

Yong Sing


I think you would be better off writing the SQL like this:

select *
from XXX.XXXX
where enddate between '2003-11-05' and '2003-11-05'
and endtime between '07.00.00' and '15.00.00'

Obviously endate is an "equal" condition in this example, but I assume that
it is not always "equal". If it is always equal, then use the = predicate
instead of between.

If you want a timestamp, maybe you should one instead of the two columns for
date and time when you create the table.
Nov 12 '05 #2

P: n/a
Mark A <ma@switchboard.net> wrote:
"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59**************************@posting.google.c om...
I have a table containing 5.1 million rows. The DDL of the table is
shown below (only relevant parts shown).

CREATE TABLE XXX.XXXX (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE DATE NOT NULL,
ENDTIME TIME NOT NULL,
...
...
PRIMARY KEY (ENDDATE,ENDTIME,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like this:

select *
from XXX.XXXX
where enddate='2003-11-05'

The query executes very fast (less than 1 second). But when I do a
query like this:

select *
from XXX.XXXX
where timestamp(enddate,endtime) between
timestamp('2003-11-05','07.00.00') and
timestamp('2003-11-05','15.00.00')

The query takes a few minutes.

Beside deleting the earlier records in the table, how else can I
improve the speed of the second query? What kind of index should I
create for the table?

Thanks in advance.

Yong Sing
I think you would be better off writing the SQL like this:

select *
from XXX.XXXX
where enddate between '2003-11-05' and '2003-11-05'
and endtime between '07.00.00' and '15.00.00'

Obviously endate is an "equal" condition in this example, but I assume
that it is not always "equal". If it is always equal, then use the =
predicate instead of between.


That would be a problem if you want, for example, the range 2003-11-05
07:00:00 thru 2003-12-01 15:00:00. The WHERE clause you gave would
eliminate all records where the endtime is not within the time range from
07:00:00 thru 15:00:00, regardless of the date.

How about this:

....
WHERE enddate BETWEEN '2003-11-05' AND '2003-12-01' AND
TIMESTAMP(enddate, endtime) BETWEEN '2003-11-05 07:00:00' AND
'2003-12-01 15:00:00'

That way, db2 can use an index on the enddate column for some filtering, and
the rest of the condition is then evaluated on the remaining rows. (Note:
the access plan should be consulted if such a processing really takes
place!)
If you want a timestamp, maybe you should one instead of the two columns
for date and time when you create the table.


--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #3

P: n/a
Thanks to the both of you for your solutions. My query may span more
than a day, so Knut's solution is better. The query is now less than a
second!

I have another problem. There is another table where the ENDDATE and
ENDTIME is combined to become a timestamp column. Here is the table
DDL:

CREATE TABLE YYY.YYYY (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE TIMESTAMP NOT NULL,
PKLINK INTEGER GENERATED ALWAYS AS IDENTITY (START WITH
-2147483648, INCREMENT BY 1),
PRIMARY KEY (PKLINK),
CONSTRAINT CONSTRAINT1 UNIQUE (ENDDATE,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like:

SELECT *
FROM YYY.YYYY
WHERE ENDDATE BETWEEN '2003-11-05 23:00:00' AND '2003-11-06 07:00:00'

I had originally thought that by creating a unique constraint on the
ENDDATE and SERIAL columns (these two combination needs to be unique),
I would have an index on the ENDDATE column and therefore the query
above would be very fast. As the table size gets bigger, I realized
that the constraint does not help at all. Should I create a separate
index on just the ENDDATE column to improve my query?

Thanks in advance.

Yong Sing
Nov 12 '05 #4

P: n/a
"yongsing" <oh********@yahoo.com.sg> wrote in message
news:59**************************@posting.google.c om...
Thanks to the both of you for your solutions. My query may span more
than a day, so Knut's solution is better. The query is now less than a
second!

I have another problem. There is another table where the ENDDATE and
ENDTIME is combined to become a timestamp column. Here is the table
DDL:

CREATE TABLE YYY.YYYY (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE TIMESTAMP NOT NULL,
PKLINK INTEGER GENERATED ALWAYS AS IDENTITY (START WITH
-2147483648, INCREMENT BY 1),
PRIMARY KEY (PKLINK),
CONSTRAINT CONSTRAINT1 UNIQUE (ENDDATE,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like:

SELECT *
FROM YYY.YYYY
WHERE ENDDATE BETWEEN '2003-11-05 23:00:00' AND '2003-11-06 07:00:00'

I had originally thought that by creating a unique constraint on the
ENDDATE and SERIAL columns (these two combination needs to be unique),
I would have an index on the ENDDATE column and therefore the query
above would be very fast. As the table size gets bigger, I realized
that the constraint does not help at all. Should I create a separate
index on just the ENDDATE column to improve my query?

Thanks in advance.

Yong Sing


Have your done the runstats command to get full statistics on the table and
indexes? If this is static embedded SQL you will need to rebind the package
afterward. Try that and let us know how it works out.

But I am also not sure about using just date and time in the literal when
comparing against a timestamp. It might be better (if runstats does not
help) if you could pad the literals in the where clause with the remaining
part of the timestamp. If the numbers are always zero, use zero for both
literals, otherwise use 000000 for the first one, and 999999 for the second
one.
Nov 12 '05 #5

P: n/a
yongsing <oh********@yahoo.com.sg> wrote:
Thanks to the both of you for your solutions. My query may span more
than a day, so Knut's solution is better. The query is now less than a
second!

I have another problem. There is another table where the ENDDATE and
ENDTIME is combined to become a timestamp column. Here is the table
DDL:

CREATE TABLE YYY.YYYY (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE TIMESTAMP NOT NULL,
PKLINK INTEGER GENERATED ALWAYS AS IDENTITY (START WITH
-2147483648, INCREMENT BY 1),
PRIMARY KEY (PKLINK),
CONSTRAINT CONSTRAINT1 UNIQUE (ENDDATE,SERIAL)
) DATA CAPTURE NONE IN USERSPACE1;

When I do a query like:

SELECT *
FROM YYY.YYYY
WHERE ENDDATE BETWEEN '2003-11-05 23:00:00' AND '2003-11-06 07:00:00'

I had originally thought that by creating a unique constraint on the
ENDDATE and SERIAL columns (these two combination needs to be unique),
I would have an index on the ENDDATE column and therefore the query
above would be very fast. As the table size gets bigger, I realized
that the constraint does not help at all. Should I create a separate
index on just the ENDDATE column to improve my query?


Have a look at the access plan to see what is happening. And as Mark
suggested, collect the statistics and look again at the access plan. If
the unique index that is used to enforce CONSTRAINT1 (note that constraints
and indexes are--logically--different tings) is not picked up, then it
could be beneficial to add a non-unique index on the ENDDATE column.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
> Have your done the runstats command to get full statistics on the table and
indexes? If this is static embedded SQL you will need to rebind the package
afterward. Try that and let us know how it works out.

But I am also not sure about using just date and time in the literal when
comparing against a timestamp. It might be better (if runstats does not
help) if you could pad the literals in the where clause with the remaining
part of the timestamp. If the numbers are always zero, use zero for both
literals, otherwise use 000000 for the first one, and 999999 for the second
one.


Hi guys, thanks for your help. I don't think I should be running
runstats now as my database is in production. I would have to wait for
an appropriate time to do so.

I did try adding the 000000 to the literals but it made no difference.
Yong Sing
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.