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:44:35 GMT
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 <Lance.Andersen@sun.com> 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 <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