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* < [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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 <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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-----
>>
>>
