Sunday, May 25, 2008

Warming Up the Hibernate Second Level Cache

I recently ran into a problem where Hibernate took forever to load up a result set because it was executing two additional queries for every object in the list to get it's @ManyToOne references. With thousands of objects returned, this meant thousands of individual sql select queries! I am using the JPA EntityManager throughout these examples.

eg:

public class MyEntity{
....
@ManyToOne
public MyEntity2 getMyEntity2(){
...
}

@ManyToOne
public MyEntity3 getMyEntity3(){
...
}

}

I had to use these many-to-one references so fiddling with lazy loading wouldn't help my situation. I was already caching the result collection after the first load, but it still was taking minutes to load up the first time and there was less than a thousand results, what gives? But 1000 * 2 extra sql select queries is 2000 individual queries which is just not good. Even the Hibernate second level cache didn't help because it still needs the first load.

There must be some way to make this startup faster right??

So I starting thinking, "how can I get these things to load up in the fewest number of sql queries?". Then it struck me, I'll warm up the cache by querying for all of the ManyToOne objects on startup, for instance:

em.createQuery("select me2 from MyEntity2").getResultList();

and

em.createQuery("select me2 from MyEntity3").getResultList();

Now when I run the original query for MyEntity, the result is almost immediate. I turned ~2000 database hits into 3!

More Reading: