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
