Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 49409 invoked from network); 14 May 2007 22:34:48 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 14 May 2007 22:34:48 -0000 Received: (qmail 42413 invoked by uid 500); 14 May 2007 22:34:53 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 42384 invoked by uid 500); 14 May 2007 22:34:52 -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 42373 invoked by uid 99); 14 May 2007 22:34:52 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 May 2007 15:34:52 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of francois.orsini@gmail.com designates 64.233.184.228 as permitted sender) Received: from [64.233.184.228] (HELO wr-out-0506.google.com) (64.233.184.228) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 May 2007 15:34:45 -0700 Received: by wr-out-0506.google.com with SMTP id m59so24079wrm for ; Mon, 14 May 2007 15:34:23 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=g2xI9LqAWB0IlLtVFilHQxoMaPuA3PxwSDFFL07Faqsq18OpzpxUoO/VSSfU9w1tmSv8nvZhziDxDoRQuDiEHEpyGOaLhb84kfiiWGRdR1hYFVz01hjuUV55n1ysPv46xDFTeLO+jcJIDjU83S38z6O/la72UCyl1Nm+XgFXwAo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=qcM7YSbBk2IQFscV4xPfP69/sp70iP3kI6lCIVcEhp7uUK4uvy2l1QTvr0PgEYGnYs35Z6zP3sy+r6Q63Wigc7FRSTO2OwctGt2mOarwCJtpbc7eAYcAMsMcHwaTlpEnsqRIJS0N7WLSnA8JZ/7pmMmoUGV2tAJmqXqJz2wGzq0= Received: by 10.114.12.9 with SMTP id 9mr1188427wal.1179182063350; Mon, 14 May 2007 15:34:23 -0700 (PDT) Received: by 10.114.197.6 with HTTP; Mon, 14 May 2007 15:34:23 -0700 (PDT) Message-ID: <7921d3e40705141534l15c5c59u2a7849c4a6f3cbf4@mail.gmail.com> Date: Mon, 14 May 2007 15:34:23 -0700 From: "Francois Orsini" To: "Derby Discussion" Subject: Re: maxrows - what does it really mean? In-Reply-To: <4648C8AB.7010704@sun.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_189163_18471377.1179182063282" References: <56a83cd00705111529p22af61e9l4a7dcff9f55fa5bb@mail.gmail.com> <3B344FED-5C5B-47FB-9E3A-9A248E96B1D1@SUN.com> <20070514114406.GE14950@atum01.norway.sun.com> <56a83cd00705140913s1d74e108p804da067c84d8b82@mail.gmail.com> <20070514194426.GA25945@localhost.localdomain> <56a83cd00705141311p1be8f1c0t7dcbd09088221f27@mail.gmail.com> <4648C8AB.7010704@sun.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_189163_18471377.1179182063282 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Right but most if not all RDBMS support a form of LIMIT. It may be non standard but support is there. On 5/14/07, Lance J. Andersen wrote: > > Also, there are not a lot of DBs that support that syntax... :-( > > David Van Couvering wrote: > > Thanks for the tip, Bernt, but I must humbly say "yuck!" to the syntax. > > > > OK, getting over that, it's pretty worthless to me given that Derby > > doesn't use it and Derby is the primary DB used by NetBeans. But > > let's say it was implemented -- would it work with a result set that > > is a join across multiple tables? I can't tell from the convoluted > > syntax... > > > > Thanks, > > > > David > > > > On 5/14/07, Bernt M. Johnsen wrote: > >> >>>>>>>>>>>> David Van Couvering wrote (2007-05-14 09:13:28): > >> > OK, so do I have it right that the right way to "hint" to the driver > >> > to not cache all one million rows when I only need ten rows is to use > >> > setMaxRows()? > >> > >> No. setFetchSize() is an optimization hint, setMaxRows() is a limit on > >> the ResultSet size. A driver may or may not communicate this to the > >> server, but the resultSet will never hold more than maxRows rows. > >> > >> > Is there a SQL standard way to "hint" to the server not to *process* > >> > all one million rows (e.g. in the order by case)? > >> > >> There's a standard SQL way to ask for an exact number of rows in the > >> query, like this > >> > >> SELECT * FROM ( > >> SELECT > >> ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, > >> columns > >> FROM tablename > >> ) AS foo > >> WHERE rownumber <= n > >> > >> Look up in the SQL standard under "window functions" for more details. > >> This is not implemented in Derby (Feature T611 Elementary OLAP > >> operations http://wiki.apache.org/db-derby/SQLvsDerbyFeatures), > >> > >> > > >> > Thanks, > >> > > >> > David > >> > > >> > On 5/14/07, Bernt M. Johnsen wrote: > >> > >What David wants, is the feature rgistered in > >> > >https://issues.apache.org/jira/browse/DERBY-581 > >> > > > >> > >>>>>>>>>>>>> Craig L Russell wrote (2007-05-13 12:06:38): > >> > >> >Also, how is maxrows related to the fetch size of a ResultSet? > >> > >> > >> > >> As I understand it, the fetch size relates to the number of rows > >> > >> returned by the server to the client for each round trip to the > >> > >> database. So theoretically the two numbers are independent. > There's > >> > >> no specified interaction except for the obvious one: requesting a > >> > >> fetch size exceeding the maxrows doesn't make sense since there > >> will > >> > >> never be more than maxrows returned, and fetch size would > >> effectively > >> > >> be ignored. > >> > > > >> > >Fetch Size is in the JDBC spec defined to be an *optimization hint* > >> > >from the application to the driver. It has no semantic meaning > >> > >whatsoever, but may e.g. influence the number of rows prefetched per > >> > >roundtrip and thus influence the overall performance of your > >> > >application. > >> > > > >> > > > >> > >-- > >> > >Bernt Marius Johnsen, Database Technology Group, > >> > >Staff Engineer, Technical Lead Derby/Java DB > >> > >Sun Microsystems, Trondheim, Norway > >> > > > >> > >> -- > >> Bernt Marius Johnsen, Database Technology Group, > >> Staff Engineer, Technical Lead Derby/Java DB > >> Sun Microsystems, Trondheim, Norway > >> > >> -----BEGIN PGP SIGNATURE----- > >> Version: GnuPG v1.4.2.2 (GNU/Linux) > >> > >> iD8DBQFGSLwalFBD9TXBAPARAjdsAJ9C1yWZCiA+G7kNwRVy81bzSQ/HsQCg2yDq > >> KUloXCu1N+PcB6BIzkkKQpY= > >> =RdA2 > >> -----END PGP SIGNATURE----- > >> > >> > ------=_Part_189163_18471377.1179182063282 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Right but most if not all RDBMS support a form of LIMIT. It may be non standard but support is there.

On 5/14/07, Lance J. Andersen < Lance.Andersen@sun.com> wrote:
Also, there are not a lot of DBs that support that syntax... :-(

David Van Couvering wrote:
> Thanks for the tip, Bernt, but I must humbly say "yuck!" to the syntax.
>
> OK, getting over that, it's pretty worthless to me given that  Derby
> doesn't use it and Derby is the primary DB used by NetBeans.  But
> let's say it was implemented -- would it work with a result set that
> is a join across multiple tables?  I can't tell from the convoluted
> syntax...
>
> Thanks,
>
> David
>
> On 5/14/07, Bernt M. Johnsen <Bernt.Johnsen@sun.com> wrote:
>> >>>>>>>>>>>> David Van Couvering wrote (2007-05-14 09:13:28):
>> > OK, so do I have it right that the right way to "hint" to the driver
>> > to not cache all one million rows when I only need ten rows is to use
>> > setMaxRows()?
>>
>> No. setFetchSize() is an optimization hint, setMaxRows() is a limit on
>> the ResultSet size. A driver may or may not communicate this to the
>> server, but the resultSet will never hold more than maxRows rows.
>>
>> > Is there a SQL standard way to "hint" to the server not to *process*
>> > all one million rows (e.g. in the order by case)?
>>
>> There's a standard SQL way to ask for an exact number of rows in the
>> query, like this
>>
>> SELECT * FROM (
>>   SELECT
>>     ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
>>     columns
>>   FROM tablename
>> ) AS foo
>> WHERE rownumber <= n
>>
>> Look up in the SQL standard under "window functions" for more details.
>> This is not implemented in Derby (Feature T611 Elementary OLAP
>> operations   http://wiki.apache.org/db-derby/SQLvsDerbyFeatures),
>>
>> >
>> > Thanks,
>> >
>> > David
>> >
>> > On 5/14/07, Bernt M. Johnsen <Bernt.Johnsen@sun.com> wrote:
>> > >What David wants, is the feature rgistered in
>> > > https://issues.apache.org/jira/browse/DERBY-581
>> > >
>> > >>>>>>>>>>>>> Craig L Russell wrote (2007-05-13 12:06:38):
>> > >> >Also, how is maxrows related to the fetch size of a ResultSet?
>> > >>
>> > >> As I understand it, the fetch size relates to the number of rows
>> > >> returned by the server to the client for each round trip to the
>> > >> database. So theoretically the two numbers are independent. There's
>> > >> no specified interaction except for the obvious one: requesting a
>> > >> fetch size exceeding the maxrows doesn't make sense since there
>> will
>> > >> never be more than maxrows returned, and fetch size would
>> effectively
>> > >> be ignored.
>> > >
>> > >Fetch Size is in the JDBC spec defined to be an *optimization hint*
>> > >from the application to the driver. It has no semantic meaning
>> > >whatsoever, but may e.g. influence the number of rows prefetched per
>> > >roundtrip and thus influence the overall performance of your
>> > >application.
>> > >
>> > >
>> > >--
>> > >Bernt Marius Johnsen, Database Technology Group,
>> > >Staff Engineer, Technical Lead Derby/Java DB
>> > >Sun Microsystems, Trondheim, Norway
>> > >
>>
>> --
>> Bernt Marius Johnsen, Database Technology Group,
>> Staff Engineer, Technical Lead Derby/Java DB
>> Sun Microsystems, Trondheim, Norway
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.2.2 (GNU/Linux)
>>
>> iD8DBQFGSLwalFBD9TXBAPARAjdsAJ9C1yWZCiA+G7kNwRVy81bzSQ/HsQCg2yDq
>> KUloXCu1N+PcB6BIzkkKQpY=
>> =RdA2
>> -----END PGP SIGNATURE-----
>>
>>

------=_Part_189163_18471377.1179182063282--