Part of my object model contains a single layer (one base class, many derived directly from it) table-per-subclass inheritance hierarchy. A feature I'm trying to implement in my application is a search tool which, among other things, allows the user to select which of those derived types they'd like to search for.

I can construct a query that queries against the base class and does the normal polymorphic query stuff, but because of the number of derived classes, this query can perform quite slowly due to the outer join of about a dozen tables. In the case where the user wants to query all types, this is probably unavoidable. However, that is a very rare case. If the user only cares about one type, there of course is not polymorphism to worry about. However, a common case is that the user selects two or three types, in which case it would be nice to optimize the query to just select from just the base table and those derived tables.

Is there a way to issue a polymorphic query with either HQL or Criteria that restricts the derived classes included? The only way I could see is to query each derived class with the where conditions, union those, and then apply the sorting to the unioned query. It would be much simpler, and perform better, if a join query like the simple polymorphic one could be issued, but with only the derived class tables I'm interested in included. Is this possible?

[b]Hibernate version:[/b]
NHibernate 1.2.1

Did you ever find a solution to this? I am having a similar issue.

- Kevin

Im not quite sure if nHibernate will be clever enough to not query the tables(im afraid it won't) if you specify types with the implicit class keyword in HQL or Criteria like described in 11.8 in the reference manual.

You can investigate if this is the case bytesting it or looking in the nHibernate source, if you fell this is really important feature you could open a jira issue with a suggestion to implement this.

Nope.

Best I've done so far is careful coding and judicious selection of where to place hibernate session boundaries and cache clearings so that the number of these large join selects is minimal.

In my case what would be really cool is if I could do a select that just queried the base class table, and returned proxy objects that would only populate the derived class items on access, since 90% of the objects returned would only have properties from the base class accessed. That might be easier to implement in the NHibernate code.

I also upgraded to PostgreSQL 8.3, which I think has some significant improvements in the query optimizer for joins of many tables, which mitigates this some. Of course that only helps you if you're using pgsql too. I'm not sure how other RDBMses compare in these type of queries.

Thanks for the update. Well I am now trying to solve my problem a different way by using SINGLE_TABLE for the subclasses. The queries are MUCH faster BUT, I can't see to get it to work with inserts. ( I am using a legacy database with a root object so I can't change that structure. )

You can view the other post here if you are interested or have thoughts on the matter.

http://forum.hibernate.org/viewtopic.php?t=984893