db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Scott Ogden" <sco...@mitsonline.com>
Subject derby performance and 'order by'
Date Fri, 16 Sep 2005 23:42:14 GMT
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


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'


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

--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;







View raw message