db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig Russell <Craig.Russ...@Sun.COM>
Subject Re: derby performance and 'order by'
Date Sat, 17 Sep 2005 21:34:50 GMT
Hi Scott,

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.

Craig

On Sep 16, 2005, at 4:42 PM, Scott Ogden wrote:

> I have observed some interesting query performance behavior and am  
> hoping someone here can explain.
>
>
>
> 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,
>
> amount numeric(31,2),
>
> time date,
>
> inv_num varchar(50),
>
> line_num varchar(50),
>
> phone varchar(50),
>
> prod_num varchar(50));
>
>
>
>
>
> --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.
>
>
>
>
> select * from orders
>
> where time > '10/01/2002' and time < '11/30/2002'
>
> order by time;
>
>
>
>
>
> --Now run a similarly query against same table, returning the top  
> 1,000 records.
>
> --The difference is that the results are now sorted by the primary  
> key (‘order_id’) rather than ‘time’.
>
> --This query returns slowly, approximately 15 seconds.  Why??
>
>
>
>
>
> select * from orders
>
> where time > '10/01/2002' and time < '11/30/2002'
>
> order by order_id;
>
>
>
>
>
> --Now run a third query against the same ‘orders’ table, removing  
> the where clause
>
> --This query returns quickly, around .010 seconds.
>
>
>
> select * from orders
>
> order by order_id;
>
>
>
> ---------------------------------------------
>
>
>
>
>
>
>
>
>
>

Craig Russell
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!


Mime
View raw message