From derby-user-return-12958-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sat Jul 10 02:48:04 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 84528 invoked from network); 10 Jul 2010 02:48:04 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 10 Jul 2010 02:48:04 -0000 Received: (qmail 94676 invoked by uid 500); 10 Jul 2010 02:48:04 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 94415 invoked by uid 500); 10 Jul 2010 02:48:02 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 94408 invoked by uid 99); 10 Jul 2010 02:48:01 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 10 Jul 2010 02:48:01 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of rbrewer@lava.net designates 64.65.64.27 as permitted sender) Received: from [64.65.64.27] (HELO outgoing03.lava.net) (64.65.64.27) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 10 Jul 2010 02:47:53 +0000 Received: from naushika.ics.hawaii.edu (naushika.ics.hawaii.edu [128.171.10.159]) by outgoing03.lava.net (Postfix) with ESMTPSA id A881410319 for ; Fri, 9 Jul 2010 16:47:31 -1000 (HST) Date: Fri, 09 Jul 2010 16:47:30 -1000 From: Robert Brewer To: derby-user@db.apache.org Subject: Unexpected slow query performance Message-ID: <746E475302F9EDC7838E29E5@naushika.ics.hawaii.edu> X-Mailer: Mulberry/4.0.8 (Mac OS X) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Checked: Checked by ClamAV on apache.org 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/