Hibernate inheritance performance

Question

I am using Hibernate 3 with a MySQL database (I tried with Hibernate 4 with no more success). I've implemented a table-per-concrete-class inheritance strategy (union-subclass).

It makes the job except for the polymorphic queries. Hibernate generates a UNION based query in which the "where" clause is in the high level query :

select primKey, param1, param2 from (
    select primKey, param1, param2 from Concrete1
    union
    select primKey, param1, param2 from Concrete2
)
where primKey == <value>
order by param1
limit 100

This leads to very bad performance as the whole concrete tables content is loaded whereas, because pkey is an attribute of the abstract parent, the "where" clause could be defined in the subselects.

So the aim would be to get Hibernate generates this kind of query :

select primKey, param1, param2 from (
    select primKey, param1, param2 from Concrete1 where primKey == <value>
    union
    select primKey, param1, param2 from Concrete2 where primKey == <value>
)
order by param1
limit 100

This way, the query is executed instantaneously.

Any idea of how I can configure Hibernate to change this behaviour ?

Thanks

Y.

not sure about hibernate but why not using a stored procedure, where let say has as input params primKey and limit; or even a view (optimized view - index etc) - the view will hold union Croncrete1 and Concrete2 tables and will have a select [cols] from mView where primKey = value order by [cols] limit ?,?. — Anda Iancu, Feb 06 '13 at 09:48

score 0 · Answer 1 · answered May 09 '13 at 19:31

I once struggled with a similar performance problem but found the only solution to be (atleast back then) to switch the implementation to "Table per class hierarchy" which gives much better performance as it requires no unions or joins. It's not as neat schema wise but if you have only a few subclasses could be good alternative.

Hibernate inheritance performance

1 Answers1