asked on

Need to speed up a SQL statement...

Hello World!

I have a select statement that takes a long time to run because I have loads of data in the table. It takes easily 10 to 15 minutes to return values...

Here is my select statement. The name of the table is "logs", the timestamp is set to 4 a.m. on Monday (today)... I'm trying to get the count of items in the table "before" the given timestamp. Any ideas how I can make this faster?

select my_id, count(distinct uid) from logs where (timestamp('2007-07-16-04.00.00.000000') - version) >= 0 group by my_id

my_id ==> CHARACTER(4)
uid ==> CHARACTER(20)
version ==> TIMESTAMP

-- Bubba

ASKER CERTIFIED SOLUTION

ghp7000

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bganoush

ASKER

Thanks...

What do you mean "do the runstats"?

Also, would it be faster if insted of subtracting the timestamp with version that I just compare the version to my timestamp?

select my_id, count(distinct uid) from logs where timestamp('2007-07-16-04.00.00.000000') >= version group by my_id

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kent Olsen

Hi Dave,

I was wondering if any of the flavors of DB2 consider this inline cast to be a function call and void the used of indexes on the column:

timestamp('2007-07-16-04.00.00.000000')

Kent

SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bganoush

ASKER

Thanks...