db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "geoff hendrey (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4675) OFFSET/FETCH SYNTAX EFFICIENCY
Date Wed, 26 May 2010 02:28:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4675?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12871483#action_12871483

geoff hendrey commented on DERBY-4675:

I have an index covering every column but the BLOB. That doesn't improve the performance.

> ------------------------------
>                 Key: DERBY-4675
>                 URL: https://issues.apache.org/jira/browse/DERBY-4675
>             Project: Derby
>          Issue Type: Improvement
>    Affects Versions:
>         Environment: unix
>            Reporter: geoff hendrey
> Using OFFSET and FETCH isn't any faster for paging than using existing JDBC methods:
> //p is page number, n is rows per page
> setMaxRows(n*p);
> setFetchSize(n);
> //...
> rs.absolute((p-1)*n);
> When used on a table with blobs, both the JDBC way, and the OFFSET/FETCH way are unexpectedly
slow ('unexpectedly' because I am not even retrieving the BLOB column).
> I need a way to do paging that doesn't slow down proportionately to the page I am requesting.
It must also maintain this performance on tables with blobs. Following is discussion from
users mail list, year or so ago.
> > 2) what are the performance implications for users of the embedded
> > driver? In particular, with the embedded driver I am hoping that
> > this feature allows portions of a result set to be retrieved without
> > the overhead of retrieving the entire result set. For example, if I
> I am afraid that with embedded driver, you will only save a little CPU
> (by avoiding some JDBC calls) since under the hood, the code siphons
> off the rows till it hits the offset, so if you have a large offset,
> you will still incur reading of those rows (modulo page caching). In
> client/server driver context the savings are larger, of course, in
> that fewer rows are sent over the wire. For simple queries that can
> use an index, the optimizer could make use of the offset information
> to avoid reading the entire row when skipping rows before offset, just
> counting rows in the index to get to the first qualifying row, but
> this optimization is not yet implemented.
> Often, this feature is used together with ORDER BY which would entail
> some sorting of the result set and then all the rows would have to be
> read anyway. Again, for some simple queries, sort avoidance is used by
> the optimizer, so optimization is still possible for for such queries.
> If you think this optimization is an important capability feel free to
> file an improvement issue for it.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message