db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Geoffrey Hendrey <geoff_hend...@yahoo.com>
Subject Re: problem with paging performance
Date Mon, 24 May 2010 13:27:06 GMT
That was my first thought. I have already tried indexing on all the columns but the pk and
the blob. Had no noticable effect.

Sent from my iPhone

On May 24, 2010, at 6:05 AM, Knut Anders Hatlen <Knut.Hatlen@Sun.COM> wrote:

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