[hibernate-dev] Reducing the memory usage of HQLQueryPlan

[Steve Ebersole]

Unless someone added support for it recently, the padding for IN is not for
Query handling, it's for batch loading which is a very different scenario.
With batch loading we know a finite number of JDBC params - the batch size.


On Fri, Aug 10, 2018, 9:17 AM Vlad Mihalcea <mihalcea.vlad at gmail.com> wrote:

> We already have the parameter padding for IN queries, and there's a PR for
> supplying ARRAY via ANY(?).
>
> For the second approach, we just have to test it thoroughly to make sure
> that all major JDBC Driver support it properly.
> Also, I'd only have the second approach activated via a config property as
> I'm not sure how efficient the Execution Plan gets with this approach.
> That also needs some testing.
>
> Vlad
>
> On Fri, Aug 10, 2018 at 4:03 PM, Christian Beikov <
> christian.beikov at gmail.com> wrote:
>
> > I'd like to note that with the array rendering strategy i.e. `where x =
> > ANY(?)` and the padding strategy i.e. pad the JDBC params, we can reduce
> > the cache trashing and avoid re-walking of the SQL-AST. IMO it's not
> > necessary to keep the SQL-AST around for the purpose of parameter
> > expansion, but maybe there are other reasons to keep it around?
> >
> > Wdyt?
> >
> >
> > Mit freundlichen Grüßen,
> > ------------------------------------------------------------------------
> > *Christian Beikov*
> > Am 09.08.2018 um 16:57 schrieb Steve Ebersole:
> > > In 6.0 HQLQueryPlan is replaced by AggregatedSelectQueryPlanImpl[1]
> > > (polymorphic queries) and ConcreteSqmSelectQueryPlan[2].
> > >
> > > ConcreteSqmSelectQueryPlan holds a reference to the SQM AST;
> > > AggregatedSelectQueryPlanImpl
> > > hold 2+ ConcreteSqmSelectQueryPlans.  AggregatedSelectQueryPlan
> operates
> > > much like HQLQueryPlan when holding more than one QueryTranslator.
> > >
> > > ConcreteSqmSelectQueryPlan ought to hold much less state (and hence use
> > > less memory) than QueryTranslator.  And it follows that
> > > AggregatedSelectQueryPlan
> > > ought to consume less memory than HQLQueryPlan+QueryTranslators.
> > >
> > > The other win, which is likely much bigger gain, is that previously
> > > parameter expansions (think `... where x in (:someListOfValues)`)
> > resulted
> > > in separate plans based on the number of values in `someListOfValues` -
> > > more HQLQueryPlan instances for the same HQL.  This is actually one of
> > the
> > > (many) explicit design goals for 6.  The cached plan is the same
> > regardless
> > > of the number of values in `someListOfValues`; the expansion happens
> > during
> > > execution.
> > >
> > > Relatedly, this last part also lets us reuse cached plans in more
> > scenarios
> > > than we do currently: enabled Filters, enabled FetchProfiles, etc.  All
> > of
> > > these things are applied/resolved during execution.  So there is a
> slight
> > > trade off between reducing the number of cached plans versus
> translating
> > > the SQM AST to a SQL AST.  This is important too because often times
> the
> > > query plan cache is "overrun" due to all of the "different cache keys"
> > > aspect (parameter list expansion, filters, etc) - so that often the
> > queries
> > > get pushed from the cache and need complete re-building.
> > >
> > > We cannot really verify this though until Luis, et.al, are able to
> > resume
> > > the performance testing...
> > >
> > > [1]
> > > https://github.com/sebersole/hibernate-core/blob/wip/6.0/
> > hibernate-core/src/main/java/org/hibernate/query/sqm/internal/
> > AggregatedSelectQueryPlanImpl.java
> > > [2]
> > > https://github.com/sebersole/hibernate-core/blob/wip/6.0/
> > hibernate-core/src/main/java/org/hibernate/query/sqm/internal/
> > ConcreteSqmSelectQueryPlan.java
> > >
> > >
> > > On Thu, Aug 9, 2018 at 9:21 AM Guillaume Smet <
> guillaume.smet at gmail.com>
> > > wrote:
> > >
> > >> Hi,
> > >>
> > >> >From what I can see, the HQLQueryPlan objects are rather big, mostly
> > due
> > >> to
> > >> the sqlAst element of the QueryTranslators.
> > >>
> > >> They can consume a fair amount of memory when you have a lot of HQL
> > >> queries.
> > >>
> > >> We need at least some of the information collected by the AST but I'm
> > >> wondering if we really need to keep all the AST information in memory
> > once
> > >> the query has been parsed and the SQL query generated.
> > >>
> > >> The purpose of this email is mostly to know if this element has been
> > >> accounted for in 6 development?
> > >>
> > >> I haven't checked if it would be doable to improve the situation
> without
> > >> breaking too much stuff in 5.x.
> > >>
> > >> Thanks.
> > >>
> > >> --
> > >> Guillaume
> > >> _______________________________________________
> > >> 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
> > _______________________________________________
> > 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