Query cache and long query strings

[klaus_erber]

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

[klaus_erber]

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>

[maxandersen]

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

[klaus_erber]

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

[maxandersen]

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 ?

[klaus_erber]

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

[maxandersen]

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)