The test was set up and run using the SQuirreL client, not ij. All 3 of the queries return the top 1000 rows and the times I reported are to return these top 1000 rows, not just the first row.
From: Craig Russell
Sent: Saturday, September 17, 2005 2:35 PM
To: Derby Discussion
Subject: Re: derby performance and 'order by'
How have you set up the test? Are you using ij and displaying all of the data or using jdbc to access the data?
What do you do in 0.010 seconds? Do you read all of the rows into
memory, or just record the time until you get the first row? Are you measuring
the time taken to return all the rows or just the first row?
Another reader has already commented on the fact that the second query is doing a lot more work than the first. The second query must sort the results after filtering the data, whereas the first and third queries can simply use the indexes and filter on the fly.
I'm a little suspicious of the third query returning 720,000 results in 0.010 seconds.
On Sep 16, 2005, at 4:42 PM, Scott Ogden wrote:
In my scenario, it appears that an existing index is not being used for the ‘order by’ part of the operation and as a result the performance of certain queries is suffering. Can someone explain if this is supposed to be what is happening and why? Please see below for the specific queries and their performance characteristics.
Here are the particulars:
create table orders(
order_id varchar(50) NOT NULL
CONSTRAINT ORDERS_PK PRIMARY KEY,
--Load a large amount of data (720,000 records) into the ‘orders’ table
--Create an index on the time column as that will be used in the ‘where’ clause.
create index IX_ORDERS_TIME on orders(time);
--When I run a query against this table returning top 1,000 records, this query returns very quickly, consistently less than .010 seconds.
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!