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

Question

I use NHibernate to load a large tree of objects of various types. Mapping is done using the table-per-subclass strategy. I defined the base class "Node", which 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 simple, and I cannot complain about performance. A large tree of 10,000 objects of various types was populated on my old car for several hundred milliseconds using one circuit. However, one thing bothers me: this strategy leads to the generation of a complex query, where the Node table is external, connected to every other table corresponding to certain subclasses. Although this is normal when the number of different subclasses is small, if the number increases, the complexity of the OUTER JOIN will also increase.

Defining a table in a class does not seem to be an elegant option, and it will work slowly when selecting data from a base class (due to UNION). Other parameters seem to increase the number of calls to the database server.

So, what do you think is the best practice when populating a large tree consisting of objects of different types? Is there anything better than a table for a subclass?

Answer 1

Here you are dealing with two problems:

• Moving a large hierarchy.

• Schedule of large objects.

Both areas where ORMs will have problems. The ORM here will be the neck for the bottles, so you may need to switch to the standard ORM functionality and go to stored procedures to handle heavy lifting when you encounter bottlenecks.

Using stored procedures, you can use the SQL execution plan to deal with problems, identify slow point queries, and identify indexing capabilities.

From Nhibernate Documentation (This Nhibernate Forge Cache was Unavailable)

Use ADO.NET with manual coding in bottlenecks.

In critical areas of the system, some types of operations (for example, bulk update / deletion) may use direct ADO.NET. But wait until you find out something is a bottleneck. And don't assume direct ADO.NET is necessarily faster. If you need to use direct ADO.NET, it may be worth it to open NHibernate ISession and use this SQL connection. That way, you can still use the same transaction strategy and core connection provider.

Answer 2

to prevent merging, you can use the table-per-classhirarchy strategy. Then each subclass is in the same table and is distinguished by a discriminator column.

I used this approach for 15 subclasses.

The only drawback is that you cannot define non-zero restrictions on the properties of subclasses, and you will have to handle this in code.