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.
On Sun, 12 Aug 2018, 04:43 Steve Ebersole, <steve(a)hibernate.org> wrote:
Pad to what? The number of elements in that passed list can
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(a)gmail.com
> 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?
> Mit freundlichen Grüßen,
> *Christian Beikov*
> Am 09.08.2018 um 16:57 schrieb Steve Ebersole:
> > In 6.0 HQLQueryPlan is replaced by AggregatedSelectQueryPlanImpl
> > (polymorphic queries) and ConcreteSqmSelectQueryPlan.
> > ConcreteSqmSelectQueryPlan holds a reference to the SQM AST;
> > AggregatedSelectQueryPlanImpl
> > hold 2+ ConcreteSqmSelectQueryPlans. AggregatedSelectQueryPlan
> > 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)`)
> > in separate plans based on the number of values in `someListOfValues` -
> > more HQLQueryPlan instances for the same HQL. This is actually one of
> > (many) explicit design goals for 6. The cached plan is the same
> > of the number of values in `someListOfValues`; the expansion happens
> > execution.
> > Relatedly, this last part also lets us reuse cached plans in more
> > than we do currently: enabled Filters, enabled FetchProfiles, etc. All
> > these things are applied/resolved during execution. So there is a
> > trade off between reducing the number of cached plans versus
> > the SQM AST to a SQL AST. This is important too because often times
> > query plan cache is "overrun" due to all of the "different cache
> > aspect (parameter list expansion, filters, etc) - so that often the
> > get pushed from the cache and need complete re-building.
> > We cannot really verify this though until Luis, et.al, are able to
> > the performance testing...
> > 
> > 
> > On Thu, Aug 9, 2018 at 9:21 AM Guillaume Smet <
> > wrote:
> >> Hi,
> >> >From what I can see, the HQLQueryPlan objects are rather big, mostly
> >> 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
> >> 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
> >> breaking too much stuff in 5.x.
> >> Thanks.
> >> --
> >> Guillaume
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev(a)lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> hibernate-dev mailing list
hibernate-dev mailing list