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 Tue, 29 Jun 2010 23:27:50 GMT

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

geoff hendrey commented on DERBY-4675:
--------------------------------------

Hi,
I wasn't able to tell what changes were made. Was it a fix?

Sent from my iPhone

On Jun 29, 2010, at 2:14 PM, "Mike Matrigali (JIRA)" <jira@apache.org> wrote:


    [ https://issues.apache.org/jira/browse/DERBY-4675?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mike Matrigali updated DERBY-4675:
----------------------------------

   Component/s: SQL

OFFSET/FETCH SYNTAX EFFICIENCY
------------------------------

               Key: DERBY-4675
               URL: https://issues.apache.org/jira/browse/DERBY-4675
           Project: Derby
        Issue Type: Improvement
        Components: SQL
  Affects Versions: 10.6.1.0
       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.




> OFFSET/FETCH SYNTAX EFFICIENCY
> ------------------------------
>
>                 Key: DERBY-4675
>                 URL: https://issues.apache.org/jira/browse/DERBY-4675
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.6.1.0
>         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.


Mime
View raw message