db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Yves Linet <jy.li...@gmail.com>
Subject Re: Ideas for optimisation needed
Date Thu, 20 Dec 2012 12:12:04 GMT
May you could give more details about the structure of your table.
Anyway the response delay you have seams to be very slow compare with what
I have with Derby.
I always read my resultset as forward only.
Try to make a first request with select count(*) to have the number of rows.
and then a second request with an order by on time field and use only
next() method.

2012/12/20 John English <john.foreign@gmail.com>

> 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

View raw message