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/
