>>>>>>>>>>>> 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]> 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
signature.asc
Description: Digital signature
