• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

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
0
bganoush
Asked:
bganoush
4 Solutions
 
ghp7000Commented:
create an index on uid, version, do the runstats, re run the query
0
 
bganoushAuthor Commented:

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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi bqanoush,

I'd write the query in a slightly more understandable form:

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


But first I'd follow the indexing advise of ghp7000:

  create index myidx000 on log (version, my_id);
  runstats on table log for index myidx000;

Note that runstats is not handled my most SQL clients.  You'll have to run that from the DB2 command line or a client that knows how to pass it to DB2.


Good Luck,
Kent

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Dave FordSoftware Developer / Database AdministratorCommented:

Bubba,

Kent's index suggestion is absolutely the FIRST thing to try.

Outside of that, it might depend on which "flavor" of DB2 you're running. (iSeries, Linux/Unix/Windows, zSeries, etc)

For instance, in DB2 for iSeries, "runstats" doesn't exist. Database statistics are collected and updated automatically.

You'd use Visual Explain (in the "database" tab of iSeries Navigator) to determine the execution plan of the query.  Most likely, it will indicate that you need to create the index that Kent advised.

Possibly, though, it may show you that the index exists, but the query can't use it. Sometimes, this happens the sort-sequence of the query is different than the sort-sequence of the tables and indices.

Start by creating the index and testing it again. If that doesn't work, post back which DB2 you're using, and we can target our comments more effectively.

HTH,
DaveSlash
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
momi_sabagCommented:
hi

first of all - the predicate
where (timestamp('2007-07-16-04.00.00.000000') - version) >= 0
is not always indexable (depends on platform and version)
but
where version <= '2007-07-16-04.00.00.000000'
is always indexable, so i would use the second one (like KDO suggested)

second of all, i would define an index on (version, my_id,uid)
this way db2 can scan only the index (no need to access the table itself) and the query will perform faster
0
 
bganoushAuthor Commented:
Thanks...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now