Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Modeling » EMF » [Teneo] 'One shot delete' of a one-to-many containment's children(...or a general solution to efficiently deleting a tree of contained objects without an excessive quantity of SQL DELETE statements)
[Teneo] 'One shot delete' of a one-to-many containment's children [message #986905] Thu, 22 November 2012 05:42 Go to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
Hi,

I'm trying to improve my delete performance. To take a simple scenario, I have a class 'Stable' that contains many 'Horse'. In other words, Stable has an EReference called 'horses', the type of which is Horse, the upper bound is -1 and it's a unidirectional containment. It's ordered, so is represented in Java as a List.

i.e.

Stable <>--> * Horse

Everything works fine, but the delete behaviour is very inefficient where I have many Horses (say, 1000). At the SQL level (via Hibernate), I get a single DELETE query for Stable, but one individual DELETE for each Horse (1000 queries).

i.e. (approximate SQL)

UPDATE Horse set StableId=null, StableIdx=null where StableId=...
DELETE FROM Horse where id=...
DELETE FROM Horse where id=...
DELETE FROM Horse where id=...
DELETE FROM Horse where id=...
...
DELETE FROM Horse where id=...
DELETE from Stable where id=...

Clearly, given that the Horse table has a foreign key reference back to the containing Stable, they could be taken out with a single query:

DELETE FROM Horse where StableId=...

I've read about a Hibernate concept called 'one shot delete', which appears to address this issue. Indeed, a Google search for 'one shot delete' reveals many forum posts from people describing exactly the problem I have (although not in the context of Teneo).

'One shot delete' in Hibernate docs: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html#performance-collections-oneshotdelete

So, prior to deleting my Stable, I've tried clearing the horses list with:

stable.getHorses().clear();

...or setting it to an empty list with:

stable.eSet(stable.eClass().getEStructuralFeature("horses"), new ArrayList<Horse>());

...and then saving the Horse. I was hoping this would cause deletion of the Horses with a single statement, after which I could go ahead and delete the Stable.

However, no matter what I've tried, I get an update query to nullify the references from Horse to Stable, followed by one delete query for each Horse.

I'm hoping the answer is to add an annotation of some sort on the Stable.horses reference, but I've not yet discovered what it is.

Thanks in advance for any help!

Regards,
Ben.

[Updated on: Fri, 23 November 2012 06:41]

Report message to a moderator

Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987086 is a reply to message #986905] Fri, 23 November 2012 06:40 Go to previous messageGo to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
I've not resolved my original problem yet (and am still very interested in any input you can provide). However, I've been thinking about alternative approaches to solving the same problem.

Sticking with my Stable <>--> * Horse example, I wonder whether I could somehow annotate my model so that responsibility for deleting Horses when their containing Stable is deleted is delegated to the database. In other words, cause 'ON DELETE CASCADE' to be defined on the foreign key reference from Horse to Stable and make sure Hibernate understands that it doesn't need to worry about Horses when deleting a Stable. Can I annotate my model such that Teneo will achieve that?

If so, then I wonder how I'd deal with a slightly more complex example, where the Horse contains a Saddle (a one-to-one unidirectional containment):

Stable <>--> * Horse <>--> 1 Saddle

In this case, the generated schema has a column in the Horse table that's a foreign key reference to its Saddle. That's fine, but means I'd end up with orphaned Saddles when I delete my Stable. Is it possible to annotate my model so that the reference is in the opposite direction; i.e. Saddle having a column that's a foreign key to the containing Horse? If so, then if that had 'ON DELETE CASCADE' set, then deletion of a Stable would clear up the whole tree. Indeed, I could then have as deep a containment tree as I like of one-to-many and one-to-one and it would always be possible to delete everything by just deleting the thing at the top.

Any comments much appreciated!

Regards,
Ben.
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987200 is a reply to message #987086] Sat, 24 November 2012 07:36 Go to previous messageGo to next message
Martin Taal is currently offline Martin Taal
Messages: 5329
Registered: July 2009
Senior Member
Hi Ben,
I see in the Teneo code that the on-delete="cascade" option has been disabled for containment associations. It would not
be too difficult to add a separate annotation for enabling this. Can you enter a bugzilla for this?
Then afaics also the multi-step cascade delete in the database would work.

Note, that I currently work in the Teneo 2.0.0 version so that's where this would be added.

gr. Martin

On 11/23/2012 12:40 PM, Ben Tenne wrote:
> I've not resolved my original problem yet (and am still very interested in any input you can provide). However, I've
> been thinking about alternative approaches to solving the same problem.
>
> Sticking with my Stable <>--> * Horse example, I wonder whether I could somehow annotate my model so that responsibility
> for deleting Horses when their containing Stable is deleted is delegated to the database. In other words, cause 'ON
> DELETE CASCADE' to be defined on the foreign key reference from Horse to Stable and make sure Hibernate understands that
> it doesn't need to worry about Horses when deleting a Stable. Can I annotate my model such that Teneo will achieve that?
>
> If so, then I wonder how I'd deal with a slightly more complex example, where the Horse contains a Saddle (a one-to-one
> unidirectional containment):
>
> Stable <>--> * Horse <>--> 1 Saddle
>
> In this case, the generated schema has a column in the Horse table that's a foreign key reference to its Saddle. That's
> fine, but means I'd end up with orphaned Saddles when I delete my Stable. Is it possible to annotate my model so that
> the reference is in the opposite direction; i.e. Saddle having a column that's a foreign key to the containing Horse?
> If so, then if that had 'ON DELETE CASCADE' set, then deletion of a Stable would clear up the whole tree. Indeed, I
> could then have as deep a containment tree as I like of one-to-many and one-to-one and it would always be possible to
> delete everything by just deleting the thing at the top.
>
> Any comments much appreciated!
>
> Regards,
> Ben.


--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987266 is a reply to message #987200] Sun, 25 November 2012 06:39 Go to previous messageGo to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
Thanks for the reply, Martin.

Incidentally, I'm stuck on Teneo 1.0.x for reasons beyond my control, but I like the way Teneo's architecture makes it easy to override behaviour, so I can probably replicate whatever change you make in 2.x somehow.

So, assuming we've got whatever we need to have 'on delete cascade' declared on all child-to-parent containment references, I'm still left with the problem of making my reference (at the db schema level) Saddle-to-Horse, rather than Horse-to-Saddle (i.e. The Horse table currently has a column for the id of the Saddle; I need it to be the other way around in order for the cascade to work). Apologies if I've overlooked something in the documentation, but can you suggest how to annotate my Horse.saddle EReference to result in a schema like that?

Coming back to my original question in the initial post, Martin, is hibernate's inefficient one-delete-query-per-row something you've encountered? If so, did you solve it by letting the database take care of cascade deletes, or is there another approach?

Thanks again for your help.

Regards,
Ben.
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987525 is a reply to message #987266] Mon, 26 November 2012 18:37 Go to previous messageGo to next message
Martin Taal is currently offline Martin Taal
Messages: 5329
Registered: July 2009
Senior Member
Hi Ben,
The easiest is if you can create an ereference/eopposite from sadle to horse and then on the horse side set this annotation:
@OneToOne(mappedBy="horse")

(this assumes that sadle has a horse property)
This helps for setting the foreign key column in the right table.

Can you enter a Teneo bugzilla for making it possible to generate a cascade delete on the association? Hopefully I have
some time around the weekend to add this. Then you can see how you can override it for your case.

gr. Martin

On 11/25/2012 12:39 PM, Ben Tenne wrote:
> Thanks for the reply, Martin.
>
> Incidentally, I'm stuck on Teneo 1.0.x for reasons beyond my control, but I like the way Teneo's architecture makes it
> easy to override behaviour, so I can probably replicate whatever change you make in 2.x somehow.
>
> So, assuming we've got whatever we need to have 'on delete cascade' declared on all child-to-parent containment
> references, I'm still left with the problem of making my reference (at the db schema level) Saddle-to-Horse, rather than
> Horse-to-Saddle (i.e. The Horse table currently has a column for the id of the Saddle; I need it to be the other way
> around in order for the cascade to work). Apologies if I've overlooked something in the documentation, but can you
> suggest how to annotate my Horse.saddle EReference to result in a schema like that?
>
> Coming back to my original question in the initial post, Martin, is hibernate's inefficient one-delete-query-per-row
> something you've encountered? If so, did you solve it by letting the database take care of cascade deletes, or is there
> another approach?
>
> Thanks again for your help.
>
> Regards,
> Ben.


--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987660 is a reply to message #987525] Tue, 27 November 2012 08:35 Go to previous messageGo to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
Raised here: https://bugs.eclipse.org/bugs/show_bug.cgi?id=395163

Regards,
Ben.
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987744 is a reply to message #987525] Tue, 27 November 2012 16:16 Go to previous messageGo to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
Hi Martin,

Martin taal wrote on Mon, 26 November 2012 18:37
Hi Ben,
The easiest is if you can create an ereference/eopposite from sadle to horse and then on the horse side set this annotation:
@OneToOne(mappedBy="horse")


That certainly gives me the Saddle-to-Horse reference I want at the DB level, and it's easy, as you say. However, if at all possible, I'd really like to avoid having to model this as bi-directional at the EMF level, as that complicates some other areas of my system. Is there an alternative technique that would achieve what I want at the schema level, but keep it as a uni-directional reference in EMF?

Thanks yet again for your assistance.

Regards,
Ben.
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #987790 is a reply to message #987744] Wed, 28 November 2012 03:03 Go to previous messageGo to next message
Martin Taal is currently offline Martin Taal
Messages: 5329
Registered: July 2009
Senior Member
Hi Ben,
I searched for a solution, see if it is possible to do this somehow in JPA but I couldn't find a solution. It seems to
be more of a JPA thing then anything else.

But if you search and find a JPA example which does what you want then let me know...

gr. Martin

On 11/27/2012 10:16 PM, Ben Tenne wrote:
> Hi Martin,
>
> Martin taal wrote on Mon, 26 November 2012 18:37
>> Hi Ben,
>> The easiest is if you can create an ereference/eopposite from sadle to horse and then on the horse side set this
>> annotation:
>> @OneToOne(mappedBy="horse")
>
>
> That certainly gives me the Saddle-to-Horse reference I want at the DB level, and it's easy, as you say. However, if at
> all possible, I'd really like to avoid having to model this as bi-directional at the EMF level, as that complicates some
> other areas of my system. Is there an alternative technique that would achieve what I want at the schema level, but
> keep it as a uni-directional reference in EMF?
>
> Thanks yet again for your assistance.
>
> Regards,
> Ben.


--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #991277 is a reply to message #986905] Tue, 18 December 2012 05:03 Go to previous messageGo to next message
Ben Tenne is currently offline Ben Tenne
Messages: 49
Registered: October 2009
Member
Hi,

I've made the relationship bi-directional, as suggested. As expected, this results in the Saddle table having a column referencing the Horse; i.e. a child-to-parent reference.

However, I can't see how to persuade Hibernate to declare ON DELETE CASCADE on this column when it generates the schema. i.e. This seems to be a Hibernate limitation, rather than Teneo. If I understand correctly, the Hibernate mapping can have on-delete="cascade" on the key element for a list, so it's useful for to-many relationships, but there doesn't seem to be a way to achieve this for to-one.

Any help much appreciated.

Thanks,
Ben.
Re: [Teneo] 'One shot delete' of a one-to-many containment's children [message #991304 is a reply to message #991277] Tue, 18 December 2012 07:37 Go to previous message
Martin Taal is currently offline Martin Taal
Messages: 5329
Registered: July 2009
Senior Member
Hi Ben,
Indeed afaics this is a hibernate limitation. So the only thing you can try is to ask this on the hibernate forum.

gr. Martin

On 12/18/2012 11:03 AM, Ben Tenne wrote:
> Hi,
>
> I've made the relationship bi-directional, as suggested. As expected, this results in the Saddle table having a column
> referencing the Horse; i.e. a child-to-parent reference.
>
> However, I can't see how to persuade Hibernate to declare ON DELETE CASCADE on this column when it generates the schema.
> i.e. This seems to be a Hibernate limitation, rather than Teneo. If I understand correctly, the Hibernate mapping can
> have on-delete="cascade" on the key element for a list, so it's useful for to-many relationships, but there doesn't seem
> to be a way to achieve this for to-one.
>
> Any help much appreciated.
>
> Thanks,
> Ben.


--

With Regards, Martin Taal

Springsite/Elver.org
Office: Hardwareweg 4, 3821 BV Amersfoort
Postal: Nassaulaan 7, 3941 EC Doorn
The Netherlands
Cell: +31 (0)6 288 48 943
Tel: +31 (0)84 420 2397
Fax: +31 (0)84 225 9307
Mail: mtaal@xxxxxxxx - mtaal@xxxxxxxx
Web: www.springsite.com - www.elver.org
Previous Topic:[CDO] ArrayIndexOutOfBoundsException in CDOIDUtil
Next Topic:How to get file which is in Project Explorer and open it from Navigator View?
Goto Forum:
  


Current Time: Sat Aug 30 22:29:32 EDT 2014

Powered by FUDForum. Page generated in 0.02211 seconds