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?
http://nextdb.net - RESTful Relational Database