CODEWITHSUNDEEP
javahibernatejpa-2.0
ballmw
ballmw

Reputation: 955

Use multiple selects instead of union in hibernate table per class inheritence

I am using TABLE_PER_CLASS inheritance. I have an abstract parent class that is an @Entity and multiple (~ a dozen) classes that extend the parent that are @Entity.

The class that references this abstract entity is marked with Fetch Lazy.

Everything functions but hibernate uses an extremely large query that unions all the tables together.

Is it possible to have hibernate use multiple select statements instead of this complicated union? If we keep adding children we possibly hit some performance issues and have to rewrite

Upvotes: 2

Views: 3003

Answers (3)

Matthew Cachia
Matthew Cachia

Reputation: 4712

Is it possible to have hibernate use multiple select statements instead of this complicated union? If we keep adding children we possibly hit some performance issues and have to rewrite

That is what we faced, on production. :) We noticed the performance was slowing down as the data grew bigger - we knew we had to shift to the JOINED strategy. Simple em.find(...) calls were causing that nasty UNION you pasted.

Using @Inheritance(JOINED) did the trick - the generated DDL's are way much neater (and faster):

SELECT 
  _p.id, ..., 
  _a.id, ..., 
  _b.id, ... 
  FROM parent AS _p 
    INNER JOIN child_a AS _a on _p.id=_a.id 
    INNER JOIN child_b AS _b on _p.id=_b.id 
  WHERE _p.id= '...';

That would use the table's own primary keys, so it's guaranteed to be fast.

Upvotes: 0

galya
galya

Reputation: 21

the difference is that when running this using the union (TABLE_PER_CLASS inheritance) , our query would like the following select a, b, c from (select a, b, c from table1 union select a, b, c from table2) where ....

meaning if we have a very big table/tables (f.e 5 million records each), and the where clause should return only 5 records from each table for example, then although we will receive 10 records in the result, and with correct index the result per table should have returned very fast, the calculation of the union will analyze all 10 million results before returning only the relevant 10 (plus our indexes won't be used since the where clause is not on the table, but on the union result

Upvotes: 2

Firo
Firo

Reputation: 30813

AFAIK unions are multiple selects, the results are just concated and returned as one resultset instead of multiple resultsets.

Upvotes: 2

Related Questions