4 Replies Latest reply on Jul 3, 2012 10:49 AM by Rico Neubauer

    Query result caching

    Rico Neubauer Novice

      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

        • 1. Re: Query result caching
          Rico Neubauer Novice

          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.

          • 2. Re: Query result caching
            Tomaz Cerar Master

            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

            • 3. Re: Query result caching
              Rico Neubauer Novice

              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.

              • 4. Re: Query result caching
                Rico Neubauer Novice

                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.