Select distinct discriminator without left join subclasses

Hibernate ORM
1

Hi. I need to fix performance without changing the data model. We have JOIN inheritance with discriminator column and 300 subclasses so attempt to fetch superclass results in huge SQL. I wanted to split into 2 HQL queries: first select distinct discriminator, then select subclasses
one by one.

For some reason even though I’m selecting only the discriminator column, the subclass tables are left joined anyway. Selecting basic attributes from the superclass work as expected.

I read about @ManyToAny, but this question is about select distinct. Please help me remove these unnecessary left joins from the generated SQL.

Here’s the test case:

2

This looks like an optimization that we didn’t do yet. Please create an improvement request in our issue tracker and attach that test case.

3

Thanks @beikov . I’ve reported HHH-17727 .
I also wanted to know: even if this improvement is done, what if instances having type= supertype exist in DB and we want to select them? In my example adding “where type(a) = VA” generates “where DTYPE=‘VA’”, but subclass tables are still left joined.

4

@Ilia_Basin thank you for opening the Jira, I think this optimization should be pretty easy to implement so I’ll try looking into it.

Regarding your latest question, it depends what you’re selecting: if you’re restricting results to a supertype e.g. where type(a) = VA but you’re selecting the entity instance itself then we need to preserve left-joins to all subclass tables, as each result might be of a specific sub-type and all its properties will be selected. If you only need e.g. the id or another property defined by the supertype we should already avoid unnecessary joins instead.

5

@mbladel Thanks, but are you 100% confident that using the special function type() in the where clause implies selecting subtypes as well? Then we probably have another bug because when there’s a discriminator column the generated SQL contains where DTYPE='name' and with that no subtypes are possible.

6

What I meant is if you’re selecting the entity instance in you query, i.e. select p from ParentEntity p where type(p) = 'subtype', the seleciton will include all possible properties of the entity instance, including subtypes. That is why all tables are left joined, even if the discriminator predicate restricts actual results to a specific type.

As I said, if you select a specific property residing in a parent class instead of the whole instance you should only get joins for the required entity types.