[Using hibernate 3.2 and postgreql]

Hi,

I have some problems and questions with performance using hiarchy and fetch joins.

I have some Enitities,
[P] - that is a superclass mappad to one table
[C1], [C2] ... [Cx] - are sub-classes to [P] and they are mapped to to diffrent tables.

Using an HQL like "select p from P where ..." hibernate will generate SQL that will fetch data for P, C1, C2 ... Cx. In this case I am only interessted in data from [P] is there a way to make hibernarte only fetch the portion of the data mapped to [P] even if the entity is a [Cx] (without doing "select p.a, p.b ... from P p where ..")?

The generated sql with many joins and case gets very slow when the table for [P] get very large ...

On to the next thing ...

There are also a number of Type-Entiites.
[T1], [T2] ... [Tx]
[P], [C1] ... are related to [T1] [T2] etc ... using FK. Where the main tables can have 200000+ rows the Tx tables have less than 100.

I tried to use HQL "select p from P p fetch join T1 .... " while this is working it seems to get very slow when the main table [P] grows. Using an HQL without fetch joins and then let hibernate fetch the related data from T1 seems to be much faster.

Should fetch join be avoided? Is there hibernate cache the best alternative to fetch the Tx fast or should I implement my own special (cache) handling for the Types?

Thank you,
/Magnus

What inheritance strategy are you using?

I suggest you "9.1.3. Table per subclass, using a discriminator"

It lets you do exactly what you want:

" The optional fetch="select" declaration tells Hibernate not to fetch the ChequePayment subclass data using an outer join when querying the superclass."

http://www.hibernate.org/hib_docs/v3/re ... criminator

I'm using annotations:
I have a BaseEntity that is @MappedSuperclass
I have a subclass to BaseEntity that is @Inheritance(strategy = InheritanceType.JOINED)
I have several subclasses to the JOINED-class

InheritanceType.JOINED seems to be the only mapping that I can use (the other are TABLE_PER_CLASS and SINGLE_TABLE).

It doesn't seem possible to do what you suggest using annotaions (?)

But is if I use fetch="select" is it then possible to do joined/sub-select fetching when I need to? ... and yes I do want to keep the cake as well as eat it ;-)

Use PolymorphismType.EXPLICIT. See reference document.

Yes, with annotations you can use PolymorphismType.EXPLICIT.

If you query for P it will only give you P fields. If you query for Cx it will give you P fields and Cx fields.

Ok I tried a few things.

PolymorphismType.EXPLICIT with InheritanceType.JOINED does not work, the SQL outer joins over all tables. The only way I can get it to generate SQL without joins is to remove the @Inheritance and by that map everything to a single table.

Does EXPLICIT only work with SINGLE_TABLE - the annotations documentation does not give me to many clues on what to try or look for ... ?

For joined subclass, Hibernate does a select from each subclass table as it does not use an explicit discriminator. So depending on which table has the data, the correct subclass is instantiated.

This is not the problem PolymorphismType.explicit tries to solve. It is just the way a joined subclass behaves.

You may have to change your mappings, if that is your requirement.

Yes what I basically trying to do is fetch the needed data as fast as possible.

I do not want to map all P and Cx to the same table, that would be a very wide table. So I do need to map P (P could/should really is abstract) and each Cx to its own table.

But in certain list-view I only list data from P, but doing "select p from P p ..." will render an SQL outer-joining all Cx. So when rendering the list-view I really just wany to fetched the common data, the data mapped to P. On the other hand, in other views I do need to have the correct subclass is instantiated.

Have not tried this yet, but this might work "select new PList(p.id, p.name) from P p ... " (?)

Or do you suggest some other way?

You may want to have a look at this and see which one suits you:


http://www.hibernate.org/hib_docs/v3/re ... tance.html


Then see how to use Hibernate Annotations for it.

If you just want the common data, you could map another class to the table P that does not have any relations and then use this 'light weight' class for the first view.

Yes I have read that document as well as the annotations document several times, but I have still not found a fool-proof solution on how to better control what data hibernate actually fetches ... and that is basically why I am asking ...

Yes mapping a second-class is actually a great idea - I will try that!

well you can do the stuff with XML mappings but not with annotations! :(

I have the same problem...,
see 9.1.3. Table per subclass, using a discriminator in documents...