[hibernate-dev] Correlating Java 1.8 Streams and ScrollableResults with database cursors

[Steve Ebersole]

As I understand it, the MySQL driver/db does not support a fetch size.  It
supports a binary choice between (1) pull all results into client  memory
or (2) stream and buffer, but without any control over the sizing of that
buffering.  If I am incorrect in that understanding then the rest here
maybe does not follow.

But if I am correct, then my concern has to do with the overhead of
streaming and buffering over small amounts of data.  Obviously there is a
tipping point where one or the other approach makes sense.  I think we just
do not have the necessary visibility into where that tipping point is.  And
in my experience picking one or the other in such binary cases where we are
just making a "shot in the dark" is usually not a good plan.

Anyway, Vladimir was going to do some testing to see how relevant the
concerns regarding overhead of stream/buffer is for smallish results.  That
too might mitigate some of my concern

On Mon, Nov 21, 2016, 12:25 PM Sanne Grinovero <sanne at hibernate.org> wrote:

> On 21 November 2016 at 18:17, Steve Ebersole <steve at hibernate.org> wrote:
> > I had a discussion about this with Vlad on the PR and privately on
> HipChat.
> >
> > I personally am -1 on this as well.  As you say Sanne the problem is "if
> you
> > all think Hibernate should be able to figure this out automatically"...
> You
> > would never be able to figure this out across all situations.
>
> I agree with that, but I guess we all equally agree that the MySQL
> "optimisation" is a dangerous default (various Search users got
> burned, I regularly have to remind about that little note in the
> docs...)
>
> We could maybe stride on the safe side, and default to small, lazy
> loading, unless the user knows better and overrides the fetch-size
> explicitly on a query?
> Only for databases which have an "unsafe default" of course.
>
> Thanks,
> Sanne
>
> >
> >
> > On Mon, Nov 21, 2016 at 12:12 PM Sanne Grinovero <sanne at hibernate.org>
> > wrote:
> >>
> >> Hi Vlad,
> >> I'm not sure if we should set the fetch size automatically, especially
> >> not globally. Could we not use the API wisely to encourage people to
> >> provide an estimate of a reasonable size, which will be different on
> >> each query?
> >>
> >> I'm aware that MySQL requires this strange value to avoid pre-loading
> >> it all in memory; in fact for example in Hibernate Search the "mass
> >> index rebuild" has to scroll on the whole dataset, so the
> >> documentation recommends to set the fetch size..
> >>
> >> However in some cases the optimisation might make sense; for example
> >> if the resultset is small, why would you use pagination when you can
> >> load it all in one single roundtrip?
> >>
> >> On the other hand, if you all think Hibernate should be able to figure
> >> this out automatically, then maybe I should also change Search to
> >> automatically set MIN_VALUE when running on MySQL, etc..
> >>
> >> Thanks,
> >> Sanne
> >>
> >>
> >>
> >>
> >> On 19 November 2016 at 10:04, Vlad Mihalcea <mihalcea.vlad at gmail.com>
> >> wrote:
> >> > Hi,
> >> >
> >> > I've noticed this new Jira ticket:
> >> >
> >> > https://hibernate.atlassian.net/browse/HHH-11260
> >> >
> >> > and I think we should correlate the Session#stream and Session#scroll
> >> > with
> >> > database cursors.
> >> >
> >> > While Oracle uses a fetch size of 10, and SQL Server uses an adaptive
> >> > cursor, PostgreSQL and MySQL just fetch the whole result set into the
> >> > client.
> >> >
> >> > So, for MySQL we could do as suggested in the Jira ticket, by setting
> >> > the
> >> > fetch size to Integer.MIN_VALUE.
> >> >
> >> > My question is about PotgreSQL. Postgres requires setting the fetch
> size
> >> > to
> >> > some positive value in order to turn the current statement into a
> >> > database
> >> > cursor.
> >> > Now, should we set a default value for streaming (e.g. 10), which can
> be
> >> > controlled via a new config property:
> >> >
> >> > hibernate.jdbc.stream.fetch_size
> >> >
> >> > Let me know what you think.
> >> >
> >> > Vlad
> >> > _______________________________________________
> >> > hibernate-dev mailing list
> >> > hibernate-dev at lists.jboss.org
> >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev at lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>