Hibernate Query Optimization

Question

I have a piece of code similar to the following:

  public void doQuery(final Baz baz){
    final Query query = getSessionFactory().getCurrentSession().createQuery(
            "select distinct foo from Foo as foo "+
            "join foo.a as a"+
            "join foo.b as b "+
            "join b.c as c "+
            "where baz=:baz"
            );
    query.setParameter("baz", baz);
    final List<Foo> list = query.list();

    for (final Foo foo : list) {
        final Set<C> cSet = foo.getB().getCs();
        final String value = foo.getSomeValue();
        for (final C c : cSet) {                
            final Long key = c.getSomeLong();
            // Do stuff with key and value
        }
    }
  }

Every time the for loop is executed it will run additional hibernate queries behind the scenes to pull the extra data (since the Objects are marked as lazy loaded). Switching those Objects to eager is not desired because other classes that use the POJO do not need that data.

Obviously the code above can create a bottleneck which is something I'd like to avoid. Is there a hibernate-specific way (i.e. no native SQL) of modifying the query to bring back only the necessary data in one shot?

I'm fine with having the query return a String[][] with col1 as the key and col2 as the value instead of returning Foo

Update:

I changed the query to just return the key/values necessary

"select distinct c.id, foo.someValue from ...

If I understand correctly, "lazy loading" may be what you're looking for. — jrochette
– jrochette, Commented Aug 17, 2012 at 14:10
First tune your sql query then just convert into Hibernate query. Couple of things, try to avoid usage of "distinct" which is heavy, also try to reduce the number of joins. — Sajan Chandran
– Sajan Chandran, Commented Aug 17, 2012 at 14:11
But won't the lazy loading essentially run the same additional queries? I'm not sure if I want to run eager loading on the POJO because other code uses that class and does not need those additional objects. That's why I was hoping I could do it in a query (although instead of returning a Foo I'd imagine it would return a String[] where col1 is the key and col2 is the value) — user973479
– user973479, Commented Aug 17, 2012 at 14:13
+1 for lazy loading. Basically mark the collections on the back of Foo as lazy. If you are using annotations then do this in the annotation (I think it's @OneToMany(fetch=FetchType.Lazy) or there is a property in the HBML. (But who uses HBML) — Dan
– Dan, Commented Aug 17, 2012 at 14:16
I just checked the POJOs and they are lazy loaded for those Objects. This still has hibernate run additional queries in the background which I am trying to avoid. — user973479
– user973479, Commented Aug 17, 2012 at 14:19

Łukasz Rzeszotarski · Accepted Answer · 2012-08-24 18:58:43Z

0

As I understand you question you don't want to have the collection eager by default but in these concrete case/method fetch all in one query?!

Here is the fragment from hibernate documentation concerning this:

A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections. See Section 19.1, “Fetching strategies” for more information.

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

answered Aug 24, 2012 at 18:58

Łukasz Rzeszotarski

6,1707 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate Query Optimization

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related