hibernate query problem, so close and stumped...

Question

The schema: (psuedocode)

I have a bean, called BaseEntity...

@Entity class BaseEntity { @OneToMany @CascadeType.ALL List [Property] properties; //the use angled braces ommited for the stackoverflow editor to show up properly }

Property is another bean...

@Entity class Property { @ManyToOne Category category; @OneToOne Value value; }

Value is actually an abstract class with Inheritence.SingleTable and subclasses like NumericalValue and DateValue, etc, as well as (in the abstract Value class) a @ManyToOne BaseType type.

The goal:

I am trying to write a query that selects BaseEntity objects that have a Property that has a Category of a certain name, and select several of them, getting objects that have any of the given properties and getting null in fields that don't exist.

The attempt:

select entity.id as id, foo as foo, bar as bar from BaseEntity entity, Value foo, Value bar where foo in (select p.value from Property p where p in elements(entity.properties) and p.category.name = 'FOO') or bar in (select p.value from Property p where p in elements(entity.properties) and p.category.name = 'BAR')

This query DOES run. Currently there is one BaseEntity in the database that matches and I get it many times over with with the correct result for foo, which it does contain, but that same entitiy over and over again with many values in the bar field.

Also, it takes like FIVE MINUTES to run and holds up everyone else using the database.

Ideas:

Of course I have considered just using some kind of distinct, but that doesn't address the extreme time it takes to run, and I just don't quite understand what's going on.

I was hoping you, my peers, could suggest a better query approach. Thank you so much! Joshua

Answer 1

Do you override the default fetch types for the associations? Usually this leads to the issue. Can you post the query Hibernate generates?

Answer 2

The performance problem looks like it is in your sub selects.

What about breaking it up a bit

select entity from BaseEntity as entity join entity.properties as property where
property.category.name in ( 'FOO','BAR' )

This would get you the list of Base Entities with a property of FOO or BAR then if you wanted to limit the collections of baseEntity.properties use a filter or requery to get the properties.

from properity where  property.category.name in ( 'FOO', 'BAR' )
and property.baseEntity.priKey = :priKey

Answer 3

It was absolutely ccclark's answer that got me in the direction, and my good friend helped too...

select entity.id as entityId, entity.type as entityType, p1.value as Vendor, p2.value as Weight from BaseEntity as entity join entity.properties as p1 join entity.properties as p2 where p1.category.name = 'Vendor' and p2.category.name = 'Total Weight'

does EXACTLY what I need. THANK YOU STACKOVERFLOW COMMUNITY!

Answer 4

That query looks reasonable to me. So the question is:

What SQL is hibernate producing for that HQL?

What query plan is the database producing for that SQL?

In Sybase or MS SQL Server: "set showplan on". In Oracle use SQLDeveloper/Toad and select the plan/Ambulance.

It is likely that you need to use an index on category name in the category table, and an index on category id and entity id in the properties table, and the primary keys, value id in the values table.