[hibernate-dev] Reducing the memory usage of HQLQueryPlan

[Vlad Mihalcea]

Pad to the nearest power of 2. The performance improvement comes from the
likelihood of reusing the SQL Execution Plan on DBs that have a cache for
that: Oracle, SQL Server, DB2.

Vlad

On Sun, 12 Aug 2018, 04:43 Steve Ebersole, <steve at hibernate.org> wrote:

> Pad to what?  The number of elements in that passed list can literally be
> anything between 1 and Interger#MAX_VALUE.  Are you saying that we should
> expand any/all multi-valued parameters to Interger#MAX_VALUE JDBC params?
>
> I guess we could do "buckets" of padding, but I am not convinced that
> really buys us any performance.  Especially when you started considering
> queries with multiple multi-valued parameters where we'd end up with a
> product of the padding buckets for each.
>
> I'm up for trying anything we think might improve performance.  But that
> implies a baseline to make a comparison anyway - so I plan on continuing
> with the current approach for now...
>
>
> On Fri, Aug 10, 2018, 8:11 AM 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