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 before.
So, problem solved it seems; many thanks for the advice! -- John English
