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

I am using NHibernate to load a large tree of different types of objects. Mapping is implemented using the table-per-sub-class strategy. I have defined a base class "node" which contains only a few fields (node ​​ID, parent ID, node type) and several sub-classes that are obtained from the node and add their own fields.

The implementation of this approach was simple and I can not complain about performance, a large tree of different types of 10 000 items is populated within a few hundred milliseconds using a round trit on its old machine. . However, there is one thing that worries me: the result of such a strategy in constructing a complex query where the node table is added to every other table according to the defined external subclasses. Although it is fine when the number of different subclasses is reduced, if the number increases, additional complexity will also increase.

Defining the table per square is not an elegant alternative, and it will work by selecting slow data while due to the base class (due to union). The other option seems to increase the number of goaltripts in the database server.

So what do you think is the best practice when creating a large tree with different types of institutions? Is the table better than the per-sub-class?

Here you are working with two issues:

Transfer to a large hierarchy.

Large object graph.

Both are those areas where ORM may have trouble. The ORM bottle is going to be neck, so you may need to step in the standard ORM methodology and go under stored procedures to handle heavy fluctuations when you face obstacles.

Using stored procedures, you can take advantage of SQL execution plans to work through issues, identify slow points for identifying queries and identifying indexing opportunities.

(This was a cache below Nhibernate Forge)

Hand-coded ADOs in the barriers. Use net.

The areas of display-critical systems, some types of operations (such as large-scale updating / delete) directly to ADO. Net can benefit but please wait, unless you know that there is some obstacle and assume that direct ADO.NET is fast if you need to use ADO.NET directly, this is a NHibernate EASON may be for opening and using that SQL connection. In this way you can still use the same transaction strategy and the built-in connection provider.