Hello,

Iam using Hibernate with Jboss Tree Cache for Second-level Caching. The entity we are caching right now has 99% reads. When we did the performance testing, the numbers did not look good.

First Issue: The DB time when doing the old way through PL/SQL is taking around ~35 ms. When using hibernate, it takes around 70ms when retrieving from cache. ( I do not see any sqls executed.)

Second Issue: When many threads try to access, it gets worse in Hibernate. Some transactions going beyond 150 ms.

Following are the details of my application:

Hibernate version: 3.1
JBoss Cache version: 1.2.1

Mapping documents:
Hibernate.cfg.xml:
<!-- Cache properties -->
<property name="hibernate.cache.provider_class">org.hibernate.cache.TreeCacheProvider</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>

Priv.hbm.xml:
<class name="com.test.PrivDTO" table="PRIV">
<cache usage="transactional"/>

<id name="privilegeId" type="long" column="privilege_id">
<generator class="sequence">
<param name="sequence">ts2owner.temp_id_seq</param>
</generator>
</id>
<property name="privilegeKey">
<column name="privilege_key" />
</property>
<property name="privilegeDescription">
<column name="privilege_description" />
</property>

<set name="userPrivs" cascade="none" lazy="true" inverse="true">
<cache usage="transactional"/>
<key column="privilege_id"/>
<one-to-many class="com.test.UserPrivDTO"/>
</set>
</class>

JBoss Cache props:
<region name="/com/test/PrivDTO">
<attribute name="maxNodes">500</attribute>
<attribute name="timeToIdleSeconds">3600</attribute>
</region>


Code snippets:
// Using from Clause
String hqlQuery = "from PrivDTO as priv where priv.userPrivs.roleId=?";
Query query = session.createQuery(hqlQuery);
query.setLong(0, roleId.longValue());
query.setCacheable(true);
List list = query.list();
for (Iterator it = list.iterator(); it.hasNext();) {
PrivDTO priv = (PrivDTO) it.next();
privs.add(priv);
}



DB: Oracle 8

We are executing more than one query though, which is the same as what pl/sql is doing.. The max number of rows we are getting is around 100. So its not huge amount of data.

Right now we are not using transactions for reads. But I tried using transactions and the numbers are worse..

Really appreciate any help.