db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: maxrows - what does it really mean?
Date Mon, 14 May 2007 22:38:57 GMT
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 
> <mailto: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
>     <mailto: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
>     <mailto: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-----
>     >>
>     >>
>
>

Mime
View raw message