db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Brewer <rbre...@lava.net>
Subject Unexpected slow query performance
Date Sat, 10 Jul 2010 02:47:30 GMT
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