Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of john.foreign@gmail.com
 designates 74.125.83.47 as permitted sender)
Message-ID: <50D5F75D.60109@gmail.com>
Date: Sat, 22 Dec 2012 20:09:33 +0200
From: John English <john.foreign@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Ideas for optimisation needed
References: <50D2FACF.7080308@gmail.com>
 <CAEe1NpArW4BgR=KM+tiYxRKQLzH-o933nsRWdw7+RTHRQMJb+w@mail.gmail.com>
 <50D45FAE.9070502@gmail.com> <87a9t7b8xk.fsf@oracle.com>
In-Reply-To: <87a9t7b8xk.fsf@oracle.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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