Hi, I'm trying to use NHibernate to handle a object inheritence mapping. Because all subclasses have different properties, I'm using the Table per Class approach.

Now, Assume P is the parent class, S1 and S2 are childrens (in the application there might be as many as 30 types of children). And another class A has a collection of P (which can be any concrete class S1 or S2). Now, the problem is, when I'm loading an instance of A, there will be many select SQL queries: one to fetch from P table, and then one for each row of the result returned. In addition, the second type of query joins P, S1 and S2. Because A can have upto 100s of Ps, I'm worried that this could downgrade performance considerably.

Because P table actually has a discriminator column for S1 and S2, I have been thinking about doing the following:
a) select PK and type from P manully group by their type.
b) for each type in the result, issue a "select in" to fetch addtional information.

However, I'd like to know whether there is any way to do this (or any other way to improve performance) without manually manange the queries (even if it's HQL).

Thanks!

I'd start off with table-per-hierarchy (TBH) rather than table-per-class (TBC) - it doesn't really matter if the attributes are not shared amongst the subclasses, but it will dramatically cut down on the number of joins, which are expensive.

Taking your example, you would have a 30-table join using TBC as opposed to one using TBH. The only caveat is if your database engine actually reserves the space for all of the field, but most don't these days

Thanks for the reply.
At this point, I'm less worried about the join than the fact that I'm sending one query per record. If the select statement has already joined all the tables, is there a way to use SELECT... IN... to batch them so that I reduce the overall # of queries?

I have found the solution: with lazy fetch and set batch-size=20, I'm able to reduce the number of queries. Thanks!