Table per-subclass strategy when populating a large tree from a database using NHibernate

Question

I am using NHibernate to load a large tree of objects of different types. Mapping is implemented using table-per-subclass strategy. I defined a base class "Node" that has only a few fields (NodeId, ParentId, NodeType) and several subclasses that inherit from Node and add their own fields.

The implementation of this approach was straightforward and I can't complain about performance. A large tree of 10 000 objects of different types is populated on my old machine within a few hundred milliseconds using a single roundtrip. However, there is one thing that worries me: such strategy results in generation of a complex query where Node table is outer joined with every other table corresponding to defined subclasses. While this is fine when the number of different subclasses are small, in case the number grows, the OUTER JOIN complexity will increase too.

Defining table per class seems to be not an elegant option, and it will work slow when selecting data from a base class (because of UNION). Other options seem to increase the number of roundtrips to the database server.

So what do you think is a best practice when populating a large tree consisting of entities of different types? Is there anything better than table per-subclass?

Answer 1

You are dealing with two issues here:

1. Traversing a large hierarchy.

2. Large object graph.

Both are areas where ORMs are going to have trouble. The ORM is going to be the bottle neck here, so you may need to side step the standard ORM functionality and drop down to stored procedures to handle the heavy lifting, when you encounter bottlenecks.

Using stored procedures you can leverage SQL's execution plan to work through issues, identify queries slow points, and identify indexing opportunities.

From the Nhibernate Documentation (This is a cache Nhibernate Forge was down)

Use hand-coded ADO.NET in bottlenecks.

In performance-critical areas of the system, some kinds of operations (eg. mass update / delete) might benefit from direct ADO.NET. But please, wait until you know something is a bottleneck. And don't assume that direct ADO.NET is necessarily faster. If need to use direct ADO.NET, it might be worth opening a NHibernate ISession and using that SQL connection. That way you can still use the same transaction strategy and underlying connection provider.

Answer 2

to prevent joins you can use table-per-classhirarchy strategy. Then every subclass resides in the same table and are discriminated with a discriminator column.

I have used this approach for 15 subclasses.

The only drawback is that you cant define not null constraints on properties of subclasses and you have to handle this in code.