db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: maxrows - what does it really mean?
Date Mon, 14 May 2007 19:44:26 GMT
>>>>>>>>>>>> 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

Mime
View raw message