Re: maxrows - what does it really mean?

[Lance J. Andersen]

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