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 <[email protected]>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 > >
