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]> 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]> 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-----
