db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Francois Orsini" <francois.ors...@gmail.com>
Subject Re: maxrows - what does it really mean?
Date Mon, 14 May 2007 22:34:23 GMT
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-----
> >>
> >>
>

Mime
View raw message