db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Ideas for optimisation needed
Date Thu, 20 Dec 2012 11:47:27 GMT
I have a table representing a logfile, with a timestamp for each entry. There 
are currently about 90,000 entries. When I display the log, I normally want to 
see the most recent entries first, so the default order is by timestamp 
descending. This typically takes about 20-25 seconds. If I want to know the 
number of entries as well, I seek to the last row of the results set, get the 
row number and seek back to the beginning again:
     res.last();
     nrows = res.getRow();   // no. of rows in result
     res.beforeFirst();
This adds about another 10 seconds.

The only thing I've come up with as a way of speeding things up is to create an 
index for the timestamp:
     CREATE INDEX log_index ON system_log (time DESC);
but this seems to make little or no difference to the timing.

Can anyone suggest how I can reorganise things so that I can view the log 
without having to wait for upwards of 30 seconds at a time?

TIA,
-- 
John English

Mime
View raw message