Query result caching

[mreasy]

Hi,

I am looking for help or hints regarding the usage of the hibernate query cache in Jboss 7.1.1.

I have entities, for which I activated the 2nd level cache using

<property name="hibernate.cache.use_second_level_cache" value="true"/>

in the persistence.xml. This works - entities themselves getcached.

However when a named query is called, this always goes through to the database, where it creates enormous "row lock contentions" and therefore is poison for throughput.

Therefore, I configured query cache as described in http://docs.jboss.org/jbossas/docs/Clustering_Guide/5/html/ch04s02s03.html and http://docs.jboss.org/hibernate/orm/4.0/manual/en-US/html/performance.html#performance-querycache with the result of the cache being created, but not being used (neither populated, nor hit) by such queries.

This is one of the NamedQueries, which shows the behavior - you can see the hibernate.cacheable hint on the query.

@Entity(name = "EMyEntity")
@NamedQueries({
    @NamedQuery(name = "EMyEntity.findByPPO",
                    query = "SELECT OBJECT(g) FROM EMyEntity g WHERE g.pk.partnerLink=:partner AND g.pk.portType=:portType AND g.pk.operation=:operation AND g.pk.env=:env ORDER BY g.createdTime DESC",
                    hints = {@QueryHint(name = "parameters", value = "java.lang.String partner, java.lang.String portType, java.lang.String operation, java.lang.String env"),
                             @QueryHint(name="org.hibernate.cacheable", value="true")})})
@Table(name = "tMyTableName")
@Cacheable
public class EMyEntity

Results always in the DB query (mention the "for update". cVersionJPA is the @Version column) on Oracle:

select cEnv
from tMyTableName
where cEnv =:1 and cOperation =:2 and cPartnerLink =:3 and cPortType =:4 and cProcessQName =:5 and cVersionJPA =:6 for update

The same happens without a named query, when doing a find-by-primary-key:

public EMyOtherEntity findById(java.lang.String id)
        throws ObjectNotFoundException
    {
        EMyOtherEntity result = em.find(EMyOtherEntity.class, id);
        // ...
        em.lock(result, LockModeType.PESSIMISTIC_READ);
        return result;
    }

which is even more confusing for me.

Am I missing sth. or any hints where to also look-at?

Best Regards,

Rico

[mreasy]

Also added some more properties, as described in https://docs.jboss.org/author/display/AS71/JPA+Reference+Guide

<shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>
        <properties>
            <!--<property name="hibernate.generate_statistics" value="true"/>-->
            <!--  caching -->
            <property name="hibernate.cache.use_query_cache" value="true"/>
            <property name="hibernate.cache.use_second_level_cache" value="true"/>
            <property name="hibernate.cache.use_minimal_puts" value="true"/>
            <property name="hibernate.cache.infinispan.statistics" value="true"/>

            <property name="hibernate.cache.region.factory_class" value="org.jboss.as.jpa.hibernate4.infinispan.InfinispanRegionFactory" />
            <property name="hibernate.cache.infinispan.cachemanager" value="java:jboss/infinispan/container/hibernate" />

            <property name="hibernate.transaction.factory_class" value="org.hibernate.transaction.CMTTransactionFactory"/>
            <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
        </properties>

but makes no difference.

[ctomc]

Hi,

you are mixing JPA1 & 2 functionailty.

query hint you are using is wrong for JPA2 since this is now standardiezed

from: http://en.wikibooks.org/wiki/Java_Persistence/Caching

JPA 2.0 Cache APIs

JPA 2.0 provides a set of standard query hints to allow refreshing or bypassing the cache. The query hints are defined on the two enum classes CacheRetrieveMode and CacheStoreMode.

Query hints:

• javax.persistence.cache.retrieveMode : CacheRetrieveMode
  • BYPASS : Ignore the cache, and build the object directly from the database result.
  • USE : Allow the query to use the cache. If the object/data is already in the cache, the cached object/data will be used.
• javax.persistence.cache.storeMode : CacheStoreMode
  • BYPASS : Do not cache the database results.
  • REFRESH : If the object/data is already in the cache, then refresh/replace it with the database results.
  • USE : Cache the objects/data returned from the query.

Cache hints example

Query query = em.createQuery("Select e from Employee e"); query.setHint("javax.persistence.cache.storeMode", "REFRESH"); 

--

tomaz

[mreasy]

Thanks Tomaz for your answer and for pointing me to those hints.

However setting explicitely those 2 to "USE" and "REFRESH" show no change in behavior - still the query result does not get cached.

Also "USE" is the default for both of those 2 hints, so should not matter whether set or not for my desired behavior.

[mreasy]

Could this be related to some fixes in the entity cache as described in https://community.jboss.org/thread/201773 ? However entity cache itself works for me, apparently only the query cache is not.