db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: derby performance and 'order by'
Date Tue, 20 Sep 2005 18:18:45 GMT
It might help to add another column to the index so that it covers both 
the restriction and the ordering information. And if we could add a 
primary key to a temporary table, then something like the following 
might take us in the right direction:

create index time_index on orders( time, orderID );

declare global temporary table session.ztemp
( orderID varchar( 50 ) primary key )
not logged;

-- all the information we need is in the index so there's no need
-- to probe the base table
insert into session.ztemp
  select orderID
  from orders
  where time between '10/01/2002' and '11/30/2002'

-- hopefully the temporary table didn't have to spill to disk.
-- no sort should be needed for this query and you can just
-- stop siphoning out rows after you get the first 1000.
select l.*
from orders l, session.ztemp r
where l.orderID = r.orderID
order by orderID;

The lighter weight our temporary tables are, the better these kinds of 
solutions will perform. Making temporary tables lighter weight could 
boost the performance of large families of problem queries.

View raw message