I'm playing around with second level caching. I have a test that loops around a block of code that creates a session, does a query, then closes the session. It displays how long each iteration in the loop takes. With caching turned on, it takes a few seconds to do the first iteration, then a few milliseconds to do the rest. All as expected. But if I turn caching off (I confirm that it's off in the hibernate output) then I get the same result! Does anyone know why? I'm using MySQL as the database.

Thanks,
shortie

1. Most database engines use caches too, and I think also MySQL does.
So when you you execute the queries for the second time, the database engines probably:
-has the queries already parsed and compiled (with the execution plan)
-has the relevant pages already in cache and has therefore not to read on disk (only logical reads, no physicals reads)

You can monitor and measure the query response time for example with p6spy.


2. Several jdbc drivers are provided with a cache for prepared statements,
so the same prepared-statement query executed for a second time, has not to be parsed anymore.
Also this could make further queries faster than the first execution.

I have also enabled query cache with all necessary configuration.
Bt I am seeing strange behaviour.

public class PartyManager {

public static void main(String[] args){
List l = main1();
List l1 = main1();
List l2 = main1();
List l3 = main1();
l3 = main1();
l3 = main1();


}

public static List<Party> main1() {
Session session = HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();
//session.setCacheMode(CacheMode.GET);

try{
long start = System.currentTimeMillis();
List l = session.createQuery("from Party p").setCacheable(true).list();// where partyTypeId=?").setString(0, "PERSON").setCacheable(true).list();
long end = System.currentTimeMillis();
System.out.println("total " + (end - start));
return l;
}
finally{
session.close();
}
}
}

Now, If I start application, it generates cache file named "Party.data".

The problem is number of times I called main1() method size of that file increases and I got delayed response. In short, The data from DB which i got for 1st time is bit faster, but the next hit to main1() method reads from cache which takes longer time. I dont know why file size is increases.
Can any1 please look into these issue.


The o/p will be as follows

May 18, 2010 5:47:42 PM net.sf.ehcache.hibernate.EhCacheProvider buildCache
WARNING: Could not find a specific ehcache configuration for cache named [org.hibernate.cache.UpdateTimestampsCache]; using defaults.
May 18, 2010 5:47:42 PM net.sf.ehcache.hibernate.EhCacheProvider buildCache
WARNING: Could not find a specific ehcache configuration for cache named [org.hibernate.cache.StandardQueryCache]; using defaults.
Hibernate: select party0_.PARTY_ID as PARTY1_0_, party0_.PARTY_TYPE_ID as PARTY2_0_ from PARTY party0_
total 3125
total 15188
total 11453
total 11515
total 11782
total 11453

Hi bshivade,

I assume you missed to configure Party to be cached in the second level entity cache.
First you have to distinct between the second level query cache and the second level entity cache.
When you use the second level query cache then it is very important to cache also the regarding entities in the second level entity cache.
This is because the second level query cache only caches the primary keys values of the records from the result-set,
assuming that the remaining values can be retrieved from the second level entity cache.
For the entities from the result-set-cache which are not cached anymore in the second level entity cache, hibernate does single lookup queries. This is still acceptable as long only few entities in meantime were evicted .
But It is hopeful clear that doing a lot of single lookup queries could become less performant as do the original query.
So generally it is necessary to configure regarding entities to be cached in the second level entity cache
and to have a survive time which is equal or greater than the survive time of the query cache [org.hibernate.cache.StandardQueryCache]

Thanks for your reply.
I have configured my Party entity for second level cache.

<class name="Party" table="PARTY" mutable="false" >
<meta attribute="implement-equals">true</meta>
<cache usage="read-only" />

<id name="partyId" column="PARTY_ID">
</id>

<property name="partyTypeId" column="PARTY_TYPE_ID" />
</class>


I want to cache all Party, party will rarely updated by external application.

My hibernate.hbm.xml is as follows
<hibernate-configuration>

<session-factory>

<!-- Database connection settings -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://127.0.0.1/ofbiz</property>
<property name="connection.username">root</property>
<property name="connection.password">root</property>

<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">4</property>

<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>

<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>

<!-- Disable the second-level cache -->
<property name="hibernate.cache.provider_class">
net.sf.ehcache.hibernate.EhCacheProvider
</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.use_second_level_cache">true</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>


<mapping resource="party.hbm.xml" />

</session-factory>

</hibernate-configuration>

If you still have the same phenomena, I suggest you to activate



and to eventually log all sql activities using p6spy.
Analyzing the log should then help to understand why subsequent queries are slower than the first.

By the way: How big is your result set ?