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, <st...@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.bei...@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.s...@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@lists.jboss.org > > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev > > >> > > > _______________________________________________ > > > hibernate-dev mailing list > > > hibernate-dev@lists.jboss.org > > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > _______________________________________________ > > hibernate-dev mailing list > > hibernate-dev@lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > _______________________________________________ > hibernate-dev mailing list > hibernate-dev@lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
