Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 1321 invoked from network); 2 May 2009 20:30:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 2 May 2009 20:30:56 -0000 Received: (qmail 84593 invoked by uid 500); 2 May 2009 20:30:56 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 84521 invoked by uid 500); 2 May 2009 20:30:56 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 84511 invoked by uid 99); 2 May 2009 20:30:56 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 02 May 2009 20:30:56 +0000 X-ASF-Spam-Status: No, hits=2.9 required=10.0 tests=HTML_MESSAGE,SPF_SOFTFAIL X-Spam-Check-By: apache.org Received-SPF: softfail (athena.apache.org: transitioning domain of tiago@espinhas.net does not designate 209.85.219.211 as permitted sender) Received: from [209.85.219.211] (HELO mail-ew0-f211.google.com) (209.85.219.211) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 02 May 2009 20:30:49 +0000 Received: by ewy7 with SMTP id 7so3490242ewy.7 for ; Sat, 02 May 2009 13:30:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.210.111.17 with SMTP id j17mr972663ebc.99.1241296225638; Sat, 02 May 2009 13:30:25 -0700 (PDT) In-Reply-To: <493507.15988.qm@web31814.mail.mud.yahoo.com> References: <493507.15988.qm@web31814.mail.mud.yahoo.com> Date: Sat, 2 May 2009 21:30:25 +0100 Message-ID: Subject: Re: The result offset and fetch first clauses From: Tiago Espinha To: Derby Discussion Content-Type: multipart/alternative; boundary=0015174c3fc29719900468f3ccf0 X-Virus-Checked: Checked by ClamAV on apache.org --0015174c3fc29719900468f3ccf0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable 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 wrot= e: > 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 all= ows > portions of a result set to be retrieved without the overhead of retrievi= ng > 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 effici= ent > 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 > =93XML? Too much like HTML. It'll never work on the Web!=94 > -anonymous > > --0015174c3fc29719900468f3ccf0 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Hello Geoff,

Right now I am able to answer 1). You can indeed use th= is 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 sor= t 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 Sa= t, 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 e= xcited if I hadn't just finished implementing this at the application-l= ayer 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 parti= cular, with the embedded driver I am hoping that this feature allows portio= ns of a result set to be retrieved without the overhead of retrieving the e= ntire result set. For example, if I have a million rows in a product catalo= g, and a user of my web app wants to sort by product name and jump to a particular portion of the resu= lt 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 foll= owing 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 lin= ks to a set of pages evenly distributes throughout the result set.
=A0
-geoff
=93XML? Too much like HTML. It'll ne= ver work on the Web!=94 <= br>-anonymous


--0015174c3fc29719900468f3ccf0--