[hibernate-dev] Reducing the memory usage of HQLQueryPlan

[Vlad Mihalcea]

I added support for IN query clause parameter padding:

https://vladmihalcea.com/improve-statement-caching-efficiency-in-clause-parameter-padding/

So, we also support this feature since Hibernate 5.2.18 and 5.3.

Vlad

On Fri, Aug 10, 2018 at 5:34 PM, Steve Ebersole <steve at hibernate.org> wrote:

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