NHibernate and cascade deletes

Question

We have a very deep object graph, and we need a way to delete a root of object graph an it needs to be happen fast. We are talking about 1000's of rows in about 10-15 tables. We also have all collections mapped as AllDeleteOrphan. We hopped that NH would execute delete by foreign key, but it actually executes delete per item in collection. How do you handle situations like this?

Answer 1

I believe you're interested in the on-delete="cascade" attribute; see the sample mapping:

    <set name="Children" table="Child" cascade="save-update" lazy="true" inverse="true">
        <key column="ParentId" not-null="true" on-delete="cascade" />
        <one-to-many class="Child" />
    </set>

A few notes on this:

1. This is only available on the inverse side of the relationship
2. You can accomplish this same mapping using an access="none" if it doesn't match your domain model cleanly (this still creates the proper cascade delete if you generate your db using schema export)

If you want to keep this behavior completely within the object model, I recommend looking at this post which discusses performing one-shot deletes using collection event listeners.

Answer 2

in situations where you need to do bulk operations, you might want to execute some hql/sql directly in a service. ORM tools provide a lot of convenience, but sometimes its better to just deal with the sql directly. We had a similar situation where we had to do a deep copy of an object graph -- took 10 minutes with hibernate, and 2 seconds with the approach i just described...

edit -- ive thought about your concern "leak anything outside of the domain", and I think you mean you don't want to mix concerns. With the approach I described, you can still have your concerns properly separated -- I said put hql methods in a service about, but I meant can do it in a DAO. All persistence code in one place.

In our situation, we spent a lot of time trying to tune hibernate and find ways to make it faster. When a senior db guy told me to just do it in hql, it took about 4 hours to implement with proper tests and it was done....you really should consider this approach to save time...