6 Replies Latest reply on Nov 7, 2006 6:46 AM by Max Rydahl Andersen

    Query cache and long query strings

    Klaus Erber Newbie

      Hello,

      we have a problem with to cache queries with long query strings. I normal cache entry looks like this (output from printDetails()):

       /sql: select zoneaggreg0_.zone as zone34_, zoneaggreg0_.site as site34_, zoneaggreg0_.intervalstart as interval3_34_, zoneaggreg0_.entId as entId34_, zoneaggreg0_.intervalType as interval5_34_, zoneaggreg0_.version as version34_, zoneaggreg0_.anzahl as anzahl34_, zoneaggreg0_.intervallaenge as interval8_34_ from stat_zone_aggregate zoneaggreg0_ where zoneaggreg0_.zone=? and zoneaggreg0_.site=? and zoneaggreg0_.entId=?; parameters: ; named parameters: {3=2, 2=2, 1=0}
      item: [11623924548, [Ljava.lang.Object;@1481b9a, [Ljava.lang.Object;@10c2a7a, [Ljava.lang.Object;@1b8e577, [Ljava.lang.Object;@1e0f6df, [Ljava.lang.Object;@6fd685, ...
      


      On longer queries, we get following output:

       /sql: select zoneaggreg0_.zone as zone34_, zoneaggreg0_.site as site34_, zoneaggreg0_.intervalstart as interval3_34_, zoneaggreg0_.entId as entId34_, zoneaggreg0_.intervalType as interval5_34_, zoneaggreg0_.version as version34_, zoneaggreg0_.anzahl as anzahl34_, zoneaggreg0_.intervallaenge as interval8_34_ from stat_zone_aggregate zoneaggreg0_ where zoneaggreg0_.zone=? and zoneaggreg0_.site=? and zoneaggreg0_.entId=? and zoneaggreg0_.intervalType=? and zoneaggreg0_.intervalstart>=? and zoneaggreg0_.intervalstart=? and zoneaggreg0_.intervalstart
      


      The cache key seems to be cut off. This is not a display problem. We get no cache hits on such a query.

      Is this a Bug? Or a wrong way to use it?

      regards
      Klaus Erber

        • 1. Re: Query cache and long query strings
          Klaus Erber Newbie

          Ok sorry, I forgot some informations about the environment:

          JBoss-4.0.5.GA ejb3-install (ejb3, Hibernate-3.2.0.GA, JBossCache 1.4.0.SP1)

          The persisitence unit:

          <persistence>
          
           <persistence-unit name="pistats">
           <jta-data-source>java:/pistats-ds</jta-data-source>
           <properties>
           <property name="hibernate.dialect"
           value="org.hibernate.dialect.DerbyDialect" />
           <property name="hibernate.hbm2ddl.auto" value="none" />
           <property name="hibernate.generate_statistics" value="true" />
           <property name="hibernate.cache.use_structured_entries" value="false" />
           <property name="hibernate.cache.provider_class" value="org.jboss.ejb3.entity.TreeCacheProviderHook"/>
          
           <property name="hibernate.treecache.mbean.object_name" value="pistats.db:service=StockEntityCache"/>
           <property name="hibernate.cache.use_query_cache" value="true"/>
           </properties>
          
           </persistence-unit>
          
          </persistence>
          


          Cache config:

           <mbean code="org.jboss.cache.TreeCache"
           name="pistats.db:service=StockEntityCache">
           <depends>jboss:service=Naming</depends>
           <depends>jboss:service=TransactionManager</depends>
          
           <attribute name="TransactionManagerLookupClass">
           org.jboss.cache.JBossTransactionManagerLookup
           </attribute>
          
           <attribute name="IsolationLevel">REPEATABLE_READ</attribute>
          
           <attribute name="NodeLockingScheme">PESSIMISTIC</attribute>
          
           <attribute name="CacheMode">LOCAL</attribute>
          
           <attribute name="LockAcquisitionTimeout">30000</attribute>
          
           <attribute name="EvictionPolicyClass">
           org.jboss.cache.eviction.LRUPolicy
           </attribute>
          
           <attribute name="EvictionPolicyConfig">
           <config>
           <attribute name="wakeUpIntervalSeconds">5</attribute>
           <region name="/_default_"
           policyClass="org.jboss.cache.eviction.LRUPolicy">
           <attribute name="maxNodes">1000</attribute>
           <attribute name="timeToLiveSeconds">1800</attribute>
           <attribute name="maxAgeSeconds">3600</attribute>
           </region>
           <region name="/stockdata"
           policyClass="org.jboss.cache.eviction.LRUPolicy">
           <attribute name="maxNodes">10000</attribute>
           <attribute name="timeToLiveSeconds">3600</attribute>
           <attribute name="maxAgeSeconds">3600</attribute>
           </region>
           </config>
           </attribute>
          
           </mbean>
          


          • 2. Re: Query cache and long query strings
            Max Rydahl Andersen Master

            I'm sorry, but I don't understand what the problem is.

            the query key consists of more than just the query string, e.g. the types, the values, the pagnation values etc.

            So wether you get a hit or not is dependent on the whole set of parameters to a query.

            /max

            • 3. Re: Query cache and long query strings
              Klaus Erber Newbie

              Sure! But when you look very close to the second key off my first post, then you will see, that key it is not complete, it is truncated.

              The first one looks good. It shows the complete query string and the query parameters.

              It seems that the keys have a size limit.

              Klaus

              • 4. Re: Query cache and long query strings
                Max Rydahl Andersen Master

                I don't know what you exactly are printing and how so that output doesn't tell me much.

                Since the one you say is "too long" is shorter than the first one then i don't see a size limit - just some weird printing.

                so what is it exactly you are printing and how ?

                • 5. Re: Query cache and long query strings
                  Klaus Erber Newbie

                  Hello Max,

                  sorry, when I'am confusing you.

                  The printings are exactly the output of the Method 'printDetails()' on the jmx-console for the two nodes in the cache.

                  The first one has a complete SQL-statement plus query parameters und result ids. I have shorten it with '[Ljava.lang.Object;@6fd685, ...' because there are many result ids.

                  The second one has no query parameters und no result ids, only an incomplete SQL-statement that is cropped.

                  It is not a display problem from 'printDetails()' because we get no cache hit, when we start the same query with the same query parameters once again.

                  Many thanks for your help
                  Klaus

                  • 6. Re: Query cache and long query strings
                    Max Rydahl Andersen Master

                    so reduce this to a testcase that doesn't require jmx and other mechanics to verify/reproduce and report it as a bug (if there is a bug)