db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tiago Espinha <ti...@espinhas.net>
Subject Re: The result offset and fetch first clauses
Date Sat, 02 May 2009 20:30:25 GMT
Hello Geoff,

Right now I am able to answer 1). You can indeed use this feature even if
you are sorting by several columns. The OFFSET and FETCH FIRST filtering is
applied over the sorted result set, so yes, you can sort as you would do
otherwise and these clauses will simply limit the portion of the result set
that is retrieved.

Hope it helps,
Tiago Espinha

On Sat, May 2, 2009 at 9:22 PM, Geoff hendrey <geoff_hendrey@yahoo.com>wrote:

> Congratulations on implementing "The result offset and fetch first
> clauses". I'd be even more excited if I hadn't just finished implementing
> this at the application-layer on top of derby 10.4, but hey, I'm happy to
> throw away my code now that derby has this internalized.
> I have a couple questions before I throw away my code:
> 1) can I use this feature even if I am ordering on multiple columns?
> 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 have a million rows in a product
> catalog, and a user of my web app wants to sort by product name and jump to
> a particular portion of the result set, I was hoping this would be efficient
> in your implementation.
> If (2) is not efficient, how does it compare to the efficiency of the
> following approach:
> Get the result set. Use a loop to increment integer n by PAGE_SIZE, and
> inside the loop use ResultSet.absolute(n) combined with stmt.setFetchSize(1)
> to retrieve a "marker" row that signifies the begining of each "page" of the
> result set. I use the primary keys of these "markers" as page boundaries so
> that my web application can provide links to a set of pages evenly
> distributes throughout the result set.
> -geoff
> “XML? Too much like HTML. It'll never work on the Web!”
> -anonymous

View raw message