From derby-user-return-6784-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon May 14 22:49:51 2007
Return-Path:
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: (qmail 53779 invoked from network); 14 May 2007 22:49:50 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
by minotaur.apache.org with SMTP; 14 May 2007 22:49:50 -0000
Received: (qmail 73154 invoked by uid 500); 14 May 2007 22:49:55 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 73126 invoked by uid 500); 14 May 2007 22:49:55 -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 73112 invoked by uid 99); 14 May 2007 22:49:55 -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:49:55 -0700
X-ASF-Spam-Status: No, hits=2.0 required=10.0
tests=HTML_MESSAGE,UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [192.18.98.43] (HELO brmea-mail-2.sun.com) (192.18.98.43)
by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 14 May 2007 15:49:47 -0700
Received: from fe-amer-02.sun.com ([192.18.108.176])
by brmea-mail-2.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l4EMnQ0i011878
for ; Mon, 14 May 2007 22:49:26 GMT
Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com
(Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006))
id <0JI100J01YNIL700@mail-amer.sun.com>
(original mail from Lance.Andersen@Sun.COM) for derby-user@db.apache.org; Mon,
14 May 2007 16:49:26 -0600 (MDT)
Received: from [192.168.1.101] ([71.243.18.89])
by mail-amer.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3
2006)) with ESMTPSA id <0JI100J98ZEDSV70@mail-amer.sun.com> for
derby-user@db.apache.org; Mon, 14 May 2007 16:49:26 -0600 (MDT)
Date: Mon, 14 May 2007 18:50:07 -0400
From: "Lance J. Andersen"
Subject: Re: maxrows - what does it really mean?
In-reply-to: <7921d3e40705141544x778d7177od82ee6e18e914960@mail.gmail.com>
Sender: Lance.Andersen@Sun.COM
To: Derby Discussion
Message-id: <4648E79F.4030903@sun.com>
MIME-version: 1.0
Content-type: multipart/alternative;
boundary="Boundary_(ID_qtDHrbkcpDKy9vmTTNAeKg)"
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>
<7921d3e40705141534l15c5c59u2a7849c4a6f3cbf4@mail.gmail.com>
<4648E501.90506@sun.com>
<7921d3e40705141544x778d7177od82ee6e18e914960@mail.gmail.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
X-Virus-Checked: Checked by ClamAV on apache.org
This is a multi-part message in MIME format.
--Boundary_(ID_qtDHrbkcpDKy9vmTTNAeKg)
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
Most drivers try and let the server side handle this so what gets passed
across the wire is the rows that meet the specified limit. This is what
we did in jConnect for example.
When this limit actually occurs could depend on how the backend applies
it and the type of query (for example if you have a sort specified)...
Francois Orsini wrote:
> I thought it would not as it is bound to the resultset (client-side)
> versus actual processing on the database engine side. I mean, if I
> only want the first 10 rows that qualifies some query, I don't want to
> have 1 million rows returned from the database engine ( e.g. server)
> as part of my resultset - LIMIT is something that database users like
> due to the fact that rows qualification and footprint is impacted
> from the database engine layer and level, not on the client side (I
> mean if I only want 10 rows, there shouldn't be more than that in the
> actual resultset.
>
> On 5/14/07, *Lance J. Andersen* > wrote:
>
> yes, most databases have a way to do that, my point was that the
> syntax below is not portable... so the driver via setmaxrows()
> should address that.
>
>
> Francois Orsini wrote:
>> 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 > > 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-----
>> >>
>> >>
>>
>>
>
--Boundary_(ID_qtDHrbkcpDKy9vmTTNAeKg)
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
Most drivers try and let the server side handle this so what gets
passed across the wire is the rows that meet the specified limit. This
is what we did in jConnect for example.

When this limit actually occurs could depend on how the backend applies
it and the type of query (for example if you have a sort specified)...

Francois Orsini wrote:

I thought it would not as it is bound to the resultset
(client-side) versus actual processing on the database engine side. I
mean, if I only want the first 10 rows that qualifies some query, I
don't want to have 1 million rows returned from the database engine (
e.g. server) as part of my resultset - LIMIT is something that database
users like due to the fact that rows qualification and footprint is
impacted from the database engine layer and level, not on the client
side (I mean if I only want 10 rows, there shouldn't be more than that
in the actual resultset.

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