db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: problem with paging performance
Date Mon, 24 May 2010 13:05:31 GMT
On 05/23/10 09:05 AM, geoffrey hendrey wrote:
> I have a table with a BLOB column. I am executing a query in which I
> do *not* select the blob. I use Statement.setFetchSize(p) to fetch a
> "page" worth of records (typically 10 rows) and
> Statement.setMaxRows(n*p) to insure that the result set covers the nth
> page. I call ResultSet.absolute((n-1)*p) to position the cursor before
> the first row of page p. There is a primary key column on every table
> that I do this paging with.
>
> This works fine for tables *without* blobs. It's very fast even to put
> advance to page 1000. For tables with blobs (typically used to store
> images that are several MB each) there is a huge performance hit even
> though I do not select the BLOB. Why is this and how can I work around it?

Hi Geoff,

I posted a comment on DERBY-4675, but I'm repeating it here for the
archives:

The inefficiency when querying a table with BLOBs may be caused by
DERBY-1506. A possible workaround for that part of the problem is to
create an index that contains all the columns referenced in the query,
so that it doesn't need to scan the heap.

Hope this helps,

-- 
Knut Anders


Mime
View raw message