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 Re: Ideas for optimisation needed
Date Sat, 22 Dec 2012 18:09:33 GMT
On 21/12/2012 19:40, Knut Anders Hatlen wrote:
> Does it change anything if you access the table directly instead of via
> the view? I think we've had reports that the optimizer cannot always see
> through the views and misses out on some opportunities for optimization.

Aha; what seems to happen is that in order to count the number of rows it reads 
though the entire result set from the view, which seems to involve formatting 
every single row...

I've ended up basically rewriting most of the code involved; I now count the 
rows in a separate query (as suggested earlier by Jean-Yves Linet), which allows 
me to use an offset/fetch clause (which are new in Derby since this code was 
originally written). The change in timing is spectacular: it now takes about 0.4 
seconds if I only want the first 20 rows, which is about 50 times faster than 

So, problem solved it seems; many thanks for the advice!
John English

View raw message