db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Van Couvering" <da...@vancouvering.com>
Subject Re: maxrows - what does it really mean?
Date Tue, 15 May 2007 17:10:52 GMT
I can imagine myself writing a nice little API that sets the offset
limit, and then have a "pluggable" implementation for each of the
drivers.  But of course, that's what JDBC is supposed to do for me :)

David

On 5/14/07, Francois Orsini <francois.orsini@gmail.com> wrote:
> Cool, would love to know how and what kind of hint is passed to the database
> engine - well, as far as Derby is concerned, setMaxRows() does not affect
> the DB engine, unless I'm mistaken - Now as far as using SQL LIMIT WITH
> OFFSET, it is up to the database engine to try and restrict the number of
> qualified rows returned to the caller....assuming the syntax is known to the
> DB ;-)
>
>
> On 5/14/07, Lance J. Andersen <Lance.Andersen@sun.com> wrote:
> >
> > 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 <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