db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoffrey Hendrey <geoff_hend...@yahoo.com>
Subject Re: Unexpected slow query performance
Date Sat, 10 Jul 2010 04:01:25 GMT
Since your data comes in ever second, couldn't you constrain the query with WHERE CURRENT_TIMESTAMP
- Tstamp < 10000

This would give you all data less than ten seconds old

Sent from my iPhone

On Jul 9, 2010, at 7:47 PM, Robert Brewer <rbrewer@lava.net> wrote:

Hello. I'm using Derby in embedded mode in a Java web application, and have run into a query
that runs slowly, though I would expect it to be fast. I'm somewhat of an SQL newbie, so please
forgive me if this is obvious.

The table in question stores power data from sensors. New rows are being inserted every few
seconds, and I want to retrieve the most recent data from a particular sensor (a "Source"
in our terminology).

The table schema is:

create table SensorData (
  Tstamp TIMESTAMP NOT NULL,
  Tool VARCHAR(128) NOT NULL,
  Source VARCHAR(256) NOT NULL,
  Properties VARCHAR(32000),
  LastMod TIMESTAMP NOT NULL,
  PRIMARY KEY (Source, Tstamp) )

I added the following indices, in the hope that they might speed things up (they did not):

CREATE INDEX TstampIndex ON SensorData(Tstamp asc)
CREATE INDEX TstampIndexDesc ON SensorData(Tstamp desc)
CREATE INDEX SourceIndex ON SensorData(Source asc)

To retrieve the most recent data entry, my query is:

SELECT * FROM SensorData WHERE Source = ? ORDER BY Tstamp DESC FETCH FIRST ROW ONLY

The table has over 3 million rows, and the select statement above is taking 10-12 seconds
to execute. Naively, I would expect to be able to find the row with most recent Tstamp fairly
quickly since it is indexed.

Any suggestions on what I can do to improve the performance of this query (hopefully to 1
second or less) would be most appreciated. Mahalo!

-- 
Robert Brewer
http://excitedcuriosity.wordpress.com/


Mime
View raw message