Cascading a Multi-Level Delete and N+1

Question

I'm finding that cascading deletes is sending SQL with many individual deletes, i.e. the N+1 problem.

I have three tables: User, UserAttribute and Attribute. Each User has many UserAttributes, each UserAttribute has one Attribute - represented by the following Maps:

public UserMap() {
  Id(x => x.Id);

  Map(x => x.Description);

  HasMany(x => x.Attributes)
    .Inverse()
    .Cascade.AllDeleteOrphan();
}

public UserAttributeMap() {
  Id(x => x.Id);

  References(x => x.User)
    .Not.Nullable();

  References(x => x.Attribute)
    .Not.Nullable()
    .Cascade.All();
}

public AttributeMap() {
  Id(x => x.Id);

  Map(x => x.Name)
    .Unique();
}

In the above test scenario my schema is then generated using FluentNH.

The problem I'm experiencing is that when I delete a User entity, the cascade generates an individual delete for each UserAttribute and each Attribute; which is a potentially large performance problem (A User could typically have hundreds of Atributes).

Is there something obviously wrong with the mapping that would be triggering the N+1 problem here? Is there anything I can do to stop it? Is this crazy talk?

---

As a side note, my NHibernate configuration already includes a large batch size, but as far as I'm aware batching won't wrap up something like this? They certainly don't appear to be batched when I profile them.

NHibernate v3.3.1.4000
FluentNHibernate v 1.4.0.0

UserTable: 
    Id
    Description

UserAttributeTable: 
    Id
    User_id (FK)
    Attribute_id (FK)

AttributeTable: 
    Id
    Name

Answer 1

The Deletion process, which you are experiecing, executed in steps one by one is there by design. The reason is:

The delete operation is significantly rare than any read operation. Therefore support for batching implemented for read - does not exist for delete.

See strong batch support for read operations:

• 19.1.5. Using batch fetching

If we really want to improve performance of the Delete process, we can use the:

• 13.3. DML-style operations cite:

...As already discussed, automatic and transparent object/relational mapping is concerned with the management of object state. This implies that the object state is available in memory, hence manipulating (using the SQL Data Manipulation Language (DML) statements: INSERT, UPDATE, DELETE) data directly in the database will not affect in-memory state. However, NHibernate provides methods for bulk SQL-style DML statement execution which are performed through the Hibernate Query Language (HQL)...

A code example from that resource:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

String hqlDelete = "delete Customer c where c.name = :oldName";
// or String hqlDelete = "delete Customer where name = :oldName";
int deletedEntities = s.CreateQuery( hqlDelete )
        .SetString( "oldName", oldName )
        .ExecuteUpdate();
tx.Commit();
session.Close();

What we can see, is the SQL statement executed on DB side without loading any entity into the app tier. That means, that we do not profit from cascading at all, but we can improve the performance.

Summary: NHibernate supports batching on Read operations. Cascade is triggered one by one. We can skip the cascade and optimize the DELETE using HQL on top of our entities