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!”