I have learned a lot about hibernate cache at the time I wrote the previous article on this subject and this brings also an interesting detail.

You are using the query and second level cache. Here is an interesting sequence:

1. You execute a query which returns items of entity A. First time it takes some time.

2. You execute the query again, it takes much less time, you are very happy :)

3. You execute another query which returns items of entity B.

4. You execute the second query again, it’s also much faster the second time, you are still very happy :)

5. You execute the first query again. It takes much, much more time that the first time. You are very, very, very sad :(

Nothing makes sense :(

Let’s see this from the hibernate cache perspective:

You are using the query and second level cache**, your cache size is 400**.

1. You execute a query which returns 350 items of entity A. First time it takes some time. Only 1 query is send to the database. The query [query->[id list]] goes to the query cache and the entities to the second level cache.

2. You execute the query again, it takes much less time, both the query and the entities are in cache, it’s fast, no query is sent to the database, you are very happy :)

3. You execute another query which returns 100 items of entity B. Only 1 query is send to the database. The query [query->[id list]] goes to the query cache and the entities to the second level cache. However 50 entities of type A are evicted from the cache!

4. You execute the second query again, it’s also much faster the second time, both the query and the entities are in cache, it’s fast, no query is sent to the database, you are still very happy :)

5. You execute the first query again. It takes much, much more time that the first time. The query is in the query cache but some of the entities are not, a lot of queries are issued to the database to load each entity which is no longer in the cache. This takes a lot of time. You are very, very, very sad :(

Now everything makes perfect sense :)

The solution? First it’s good to understand the cause, one solution is to use batch-size on the class itself, for instance:

<pre lang="xml"><class batch-size="640" name="LocalizedData" table="LOCALIZED_DATA"></class>

this way instead of 50 loads you will have fewer loads of type:

<pre lang="sql">select ... from LOCALIZED_DATA where id in (?, ?, ? ...)

Check this wonderful link which explains how many loads will actually be done given a batch-size value.