NHibernate One Shot Delete

The other day, a coworker showed me a database delete function was taking a significantly long time to complete and in some cases caused a web request to timeout.  We started investigating the situation and found a quirk with the NHibernate one shot delete.

Parent Mapping

The parent domain object in question is referenced by three tables in the database.  We implemented bi-directional referencing between these objects and mapped the collections in the parent as inverse cascade delete.  The intention was to make the management simpler because deleting the parent object would delete all child objects as well.

Technically, this is exactly how NHibernate operated.  But, thanks to the MVC Mini Profiler, we learned that NHibernate queried the database for all child objects and executed a delete query for each one.

This caused a significant performance problem since our web application was trying to delete ten thousand objects.  But what caused concern is how would this process scale if we needed to delete several million objects at a time?  And since this is an anticipated scenario for this application we knew we needed to do something.

The NHibernate One Shot Delete Quirk

I started by scouring the NHibernate documentation and more specifically the section on one shot delete.  After reading this section I’m under the impression that NHibernate is capable of deleting each object one at a time or deleting all objects in collection with a single query.  And, at the time of writing this post, the documentation states that you are capable of forcing NHibernate to use the second method as mentioned below:

Fortunately, you can force this behaviour (ie. the second strategy) at any time by discarding (ie. dereferencing) the original collection and returning a newly instantiated collection with all the current elements. This can be very useful and powerful from time to time.

one-shot-delete apply to collections mapped inverse="true".

I tested the dereferencing the collections in several ways but did not observe the behavior stated. After further searching I found a Google Group Discussion that described a similar observation and complaint.  Unfortunately, the documentation appears to be incorrect and if objects are mapped bi-directionally with inverse=”true” the one shot delete is not possible.

Solutions and Other Options

A solution suggested in this Stack Overflow post is to make the relationships uni-directional and remove the inverse=”true” mapping.  Additionally since the inverse setting would be false then the cascading would change to cascade delete all orphaned.  The behavior of NHibernate’s cascade delete all orphaned setting is to first update the reference column of the child objects to null, delete the parent object, and lastly delete any child objects that contain a null value in the reference column.  This process appears to be highly effective and efficient.

Unfortunately, we are not able to implement uni-directional referencing or cascade all delete orphaned for our web application.  Instead we turned to custom SQL strings using the “CreateSQLQuery” function.  This solution gave us the performance improvement that we needed and we plan to use Named SQL Queries to call a stored procedure to execute this delete in the future.