We have an inheritance relationship that has a base class with many (20 or so) derived classes. When querying on the base class, we see Hibernate automatically generating a left outer join to determine the subclass. The query generated is massive because it lists all columns on all tables. We haven't experienced any problems yet, but are concerned that we may hit some limit (i.e. SQL limit).

Is this a concern that we have to address in our architecture of our application or does Hibernate internally prevent these sort of SQL limit issues.

Thanks in advance,

Rich Christy

So you are using table-per-subclass mapping?

In some sense Hibernate is only doing what you tell it to do. You have one table per class, and you are querying for all objects in all tables, so Hibernate has to hit all the tables.

Yes, I would imagine that at some point you will hit an SQL limit. (If you have 5000 subclasses, then you can't join 5000 tables!)

Why so many subclasses? Why not use table-per-class-hiearchy?

There is probably a way to tune this but I don't know what it would be. On your end, you could always query for only subsets of the subclasses and then join them together in your application code....

Cheers,
Rob

Rob,

Thank you for responding. We did some testing using Oracle. It is actually a function of number of columns instead of number tables. If the combined number of columns in all derived tables exceeds 1000, than problems will occur (at least with Oracle). In fact we experimented with some of our larger tables (mapped it several times in using different objects just to see what happens) and we could get Hibernate to generate SQL that exceeded the limit allowed by Oracle.

Unfortunately we are mapping in tables to an existing, large, financial database, so we don't have the luxury of changing our schema. Currently we have only a combined 400 columns mapped, so we are far from the 1000 limitation; it is just a limitation we need to be aware of as we add more products to this application.

What would be ideal is if Hibernate allowed us to use discriminators on joined-subclasses. However I read in the docs where it was actually a design decision to not allow discriminators with the joined-subclass inheritance model. Any chance that this may change in future releases of Hibernate?

Thanks again,

Rich

Search the forum on that subject, it has been discussed. But I can remember Gavin not to be fond of that at all.

Hello!

I have similar architecture and also not satisfied with current querying strategy. But... Yes i've searched forum and saw Gavin's attitude. :) Well, one might like or dislike such approach but there are a number of [good working] systems where it is used.

So, my question: is it possible with little casualities to write a custom persister which will employ different strategy. For example, we could do it like this:
1. Specify a discriminator
2. Make a "long" SQL batch (instead of "wide" statement with joins) which will first select discriminator and then make a joined statement specially for this type.
2.1 Alternatively, we could make several selects (from base table, from derived, from derived and so on), retrieve them as sequence of result sets and join on the client side.
3. As this batch will be most possibly quite long, we have to prepare it once and lately use prepared statement. I saw that Hibernate already preparing statements, but it also noticed that it frees them almost immediately.

P.S. Actually, i was playing with NHibernate, so some of suggested concepts could be wrong for java world.

As I keep saying, this strategy is just too vulnerable to N+1 problems. All you are doing is doing the join in memory, instead of on the db. Why should that be faster??

Assume a heirarchy with 20 classes, to depth about 3 or 4.

Lets say I issue a polymorphic query against the superclass, and get back 15 instances. In your strategy, I would need a further 15 queries, each joining 2 or 3 tables to fetch the rest of the data. These extra queries can't really be batched, since the instances are all of different subclasses and so each query touches different tables.

By contrast, Hibernate's strategy uses a single join across 20 tables. This is likely to be much more efficient. I still touch exactly the same tables, exactly the same indexes, exactly the same rows, but I do it all in one request!

OK now, suppose my query would return 200 instances. Depending upon whether I can efficiently batch the "extra" queries, I get at best 1 + 20 queries, at worst 1 + 200. Meanwhile, Hibernate's strategy still results in just one query.

And for a query against a concrete subclass, the two strategies both result in a single query with a join across 3 or 4 tables.

The only case where your suggested strategy might be faster (and I don't necessarily accept that it would be) is for polymorphic queries against the superclass which return only very small numbers of instances. But optimizing for this case seems absurd, right? You should optimize for the cases which are likely to cause problems, I guess.

QED

We actually have a financial application using an Oracle database where we have a base class with many subclasses (currently about 30 subclasses, with a combined number 400 columns). What we have noticed is the more subclasses we add, the slower and larger the query is getting (our DBAs believe that it takes Oracle a measurable amount of time just to parse the query). In our application, querying through the base class is our primary use case. What my concern is that eventually we will hit an Oracle limit to this massive query (either size, number of columns, tables, etc.).

In Hibernate 3, the DTD allows you to add discriminators to joined-subclasses. Couldn't that be used to generate 2 queries instead of the outer join of several tables (assume we are querying on primary through the base class) - the first against the base table using the discriminator and the second directly against the correct subclass table(s). Before using Hibernate, we where effectively doing this ourselves since we were using JBoss CMP and we can't use inheritance with EJBs. What we noticed in our application that the two queries where faster than the one large outer join query.

Rich

Oracle should NOT need to parse the query each time, assuming that prepared statement caching is enabled.



The DTD allows it, but the discriminator will be ignored for this strategy.



Read my argument again. Sure, two queries might be faster than one huge query. But will 31 queries be faster than one huge query? No way!

You are concentrating on the trivial case of a query that returns a single instance. Think about queries which return many instances. Your strategy does not scale. Mine does.

No you're right, 31 queries would not as fast as 1 query. My concern isn

In my view, huge inheritance heirarchies are just a Bad Idea, and you should avoid them. There is no mapping strategy which will not eventually run into performance problems.

There is one exception: the case where the subclasses are mainly stateless, and we can use table-per-heirarchy. This is typical for a strategy pattern, for example.

Thanks Gavin for your time.

Rich

Because we 1) minimize data transfer between server and client (at least transfer of metadata) and 2) significatnly decrease the complexity of queries. Even if query is prepared server have to join 20 tables on each request. This must have impact on performance.



It could be batched if more complex script will be used. For example, iterate through cursor and perform appropriate selects. Note, i'm not insisting on such scenario, i'm just telling that it is possible to execute whole request in one trip to server.



Actually, this is my situation. Our system already has a query subsystem and i wanted to use Hibernate to retrieve objects by their IDs.



This is good point. I didn't realize that Hibernate uses another joining stategy to query subclasses. In this case, users of archtecture like ours could just don't use polymorphic queries at all.

I am having the same sort of problem and I AM using the table-per-heirarchy pattern. The problem is that I also have a complex graph.

I have a class A, which is flat, and classes B, C, D, and E, which are heirarchies, each heirarchy having a single table. Associations exist from A to B to C to D to E, all of which are many-to-one or one-to-one. Each subclass of B, for example, may have a different set of many-to-one associations to different subclasses of C.

I want to query like this:



Now it is clear from the mappings that a proper query of this graph should result in not such a large graph and maybe 30 joins. However, the SQL query that results has almost 100 joins...basically it joins for every many-to-one or one-to-one association even though these are not relevant to the discriminator for the record I want. The result is "ORA-01792: maximum number of columns in a table or view is 1000".


I tried queries like



assuming I would use another query to get the sublass name, hoping that this would narrow the query, but it didn't help.

I am able to make small changes to the Java classes if necessary, but a refactoring of the object model is not permitted.

How can I get Hibernate to create a more directed query?[/quote]

Now that I think about it, my problem may be different from the one at the beginning of this thread, since it could be solved if the associations were just not queried until I navigated to them. Why are they in fact, being fetched eagerly? As you can see from my HQL above, I have no joins, explicit or otherwise.

OK, it turns out that you have to specify the class and all subclasses of your heirarchies as lazy="true" if you want it to work correctly.