6

I am using Hibernate support for TABLE_PER_CLASS inheritance strategy. Functionality wise it works well. Whenever polymorphic query is issued Hibernate generates an SQL containing "union all" for my two concrete classes A & B. The generated SQL has the following format:

select C1, C2, C3 from (
    select C1, C2, C3 from ClassA
  union all
    select C1, C2, C3 from ClassB
)
where 
    C1 == <value>
order by C2
limit 100

The problem this approach suffers from really bad performance on the DB side. Taking into account C1 column is a shared property of ClassA and ClassB (derived from abstract parent) Hibernate could insert the where clause in both sub selects and dramatically improve performance. For example,

select C1, C2, C3 from ( 
    select C1, C2, C3 from ClassA where C1 == <value>
    union all
    select C1, C2, C3 from ClassB where C1 == <value>
)
order by C2
limit 100

Some optimization could also be done on the limit. I am using Hibernate criteria API in my DAO layer.

Interceptor, onPrepareStatment() could not be used since arguments are not visible. Using partitions and possibly other options on the DB is currently out of the scope as we want to avoid DB specific optimization at this phase of the work.

Any idea how to manipulate hibernate to improve performance?

user1392212
  • 61
  • 3
  • Can you be more clear - give the SQL query generated now and the variant that would result in better performance. – gkamal May 13 '12 at 16:14
  • What is the underlying database? I'd guess it wouldn't be Oracle, as it usually pushes down predicates into subselects and into unions... Does `C1` have an appropriate index in both tables? – Lukas Eder May 14 '12 at 12:41
  • C1 do have an index on both tables. We may end up supporting multiple DBs, among them Derby (Pure java). As a result the current focus is to define a schema which will yield best performance for all Databases. – user1392212 May 15 '12 at 04:53

1 Answers1

0

I you want high cross-database performance than I'd recommend not using table-pr-class and many or huge polymorphic queries. A discriminator column would usually be better.

Note that you can combine table-pr-class with discrimantor-columns if you have more than two levels in your class hierchy.

dpnmn
  • 493
  • 2
  • 7
  • Thanks for the answer. Yet, I am trying to avoid huge table which 99% of the time 90% of its data is not needed by splitting the tables. – user1392212 May 25 '12 at 05:36