Re: [hibernate-dev] 6.0 - multi-table mutations
On Tue, Mar 26, 2019 at 10:17 AM Sanne Grinovero wrote: > > Why should we not? > There is a very compelling reason for that. I'll have to leave it at that. ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] 6.0 - multi-table mutations
On Tue, 26 Mar 2019 at 15:08, Steve Ebersole wrote: > > On Tue, Mar 26, 2019 at 9:57 AM Sanne Grinovero wrote: >> >> One question: could we benefit from "cascade delete" rules defined in >> the table structure? >> >> >> If Hibernate ORM was able parse the cascading rules from existing >> schemas, and possibly generate them for new schemas, you could have an >> entire crop of additional such optimisations. I'm not sure if I'm >> talking about material for ORM 7+ but if you could make sure that such >> future optimisations aren't ruled out that would be awesome :) > > > Good point. > > We actually do already have some limited support for cascading FKs in ORM - > `org.hibernate.annotations.OnDelete`. And it is exported to the database if > defined. We do not (nor should we) parse this information from the existing > schema. Why should we not? > > What you are asking about specifically would require us to round out that > support (secondary tables at least are not yet supported) and of course to > leverage that in the HQL bulk deletion handlers. Not impossible or even > particularly difficult - but enough work that I'd rather that be handled > somewhere post-6.0 +1 fair enough ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] 6.0 - multi-table mutations
On Tue, Mar 26, 2019 at 9:57 AM Sanne Grinovero wrote: > One question: could we benefit from "cascade delete" rules defined in > the table structure? > If Hibernate ORM was able parse the cascading rules from existing > schemas, and possibly generate them for new schemas, you could have an > entire crop of additional such optimisations. I'm not sure if I'm > talking about material for ORM 7+ but if you could make sure that such > future optimisations aren't ruled out that would be awesome :) > Good point. We actually do already have some limited support for cascading FKs in ORM - `org.hibernate.annotations.OnDelete`. And it is exported to the database if defined. We do not (nor should we) parse this information from the existing schema. What you are asking about specifically would require us to round out that support (secondary tables at least are not yet supported) and of course to leverage that in the HQL bulk deletion handlers. Not impossible or even particularly difficult - but enough work that I'd rather that be handled somewhere post-6.0 ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
Re: [hibernate-dev] 6.0 - multi-table mutations
Hi Steve, this does sound amazing, I think it's certainly worth trying to have the most efficient strategy chosen on a per-entity base. One question: could we benefit from "cascade delete" rules defined in the table structure? If Hibernate ORM was able parse the cascading rules from existing schemas, and possibly generate them for new schemas, you could have an entire crop of additional such optimisations. I'm not sure if I'm talking about material for ORM 7+ but if you could make sure that such future optimisations aren't ruled out that would be awesome :) Thanks, Sanne On Tue, 26 Mar 2019 at 13:25, Steve Ebersole wrote: > > While working on 6 I discovered that we maybe do not do the best job in > regards to "bulk update/delete" queries against multi-table entities > (secondary tables, joined inheritance, etc). This short-coming exists in > previous versions. Essentially, when a strategy is not explicitly > specified we fallback to using an "id table" and performing the SQL updates > or deletes using the id table as a sub-query. The problem is that for some > databases this breaks when the ids are composite due to the database not > having complete support for tuples. For example, H2 does not allow the > sub-query for an in-predicate to return more than one column: so a query > like `... where (id1, id2) in (select val1, val2 from ...)` will not work. > This tuple concern is something I had not considered when I first wrote > this feature. > > Obviously with 6 we have a chance to improve this. So I have been thinking > about some ways this might be improved. The guiding principle here was to > make this as implicit as possible... > > Because certain strategies will not work when the ids are composite, I > think the first consideration is whether we want to allow the strategy to > be definable per-entity. The idea would be to allow for the most efficient > strategy to be used generally (whatever that might be for the given > app/database) but to pick an alternative strategy in cases where that > "fallback" one will not work. The Dialect should certainly play a part in > this strategy selection. > > And lastly, we should consider whether it is beneficial to leverage these > strategies when performing normal mutations (merge, persist, etc). I think > really this comes down to whether we think there is a benefit to handling > these via CTE if the database supports that as opposed to sending > individual updates or deletes against each table. > > Thoughts? > ___ > 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
Re: [hibernate-dev] 6.0 - multi-table mutations
https://hibernate.zulipchat.com/#narrow/stream/132094-hibernate-orm-dev/topic/6.2E0.20-.20multi-table.20mutation On Tue, Mar 26, 2019 at 8:25 AM Steve Ebersole wrote: > While working on 6 I discovered that we maybe do not do the best job in > regards to "bulk update/delete" queries against multi-table entities > (secondary tables, joined inheritance, etc). This short-coming exists in > previous versions. Essentially, when a strategy is not explicitly > specified we fallback to using an "id table" and performing the SQL updates > or deletes using the id table as a sub-query. The problem is that for some > databases this breaks when the ids are composite due to the database not > having complete support for tuples. For example, H2 does not allow the > sub-query for an in-predicate to return more than one column: so a query > like `... where (id1, id2) in (select val1, val2 from ...)` will not work. > This tuple concern is something I had not considered when I first wrote > this feature. > > Obviously with 6 we have a chance to improve this. So I have been > thinking about some ways this might be improved. The guiding principle > here was to make this as implicit as possible... > > Because certain strategies will not work when the ids are composite, I > think the first consideration is whether we want to allow the strategy to > be definable per-entity. The idea would be to allow for the most efficient > strategy to be used generally (whatever that might be for the given > app/database) but to pick an alternative strategy in cases where that > "fallback" one will not work. The Dialect should certainly play a part in > this strategy selection. > > And lastly, we should consider whether it is beneficial to leverage these > strategies when performing normal mutations (merge, persist, etc). I think > really this comes down to whether we think there is a benefit to handling > these via CTE if the database supports that as opposed to sending > individual updates or deletes against each table. > > Thoughts? > ___ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev