Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] caching query results for all queries...? 
Hi Tim,
At this point the only thing I can think of to help you is to somehow get a reproduction of the problem for you. If that's possible, you can either share it here, or we can find a non-public way to share the code.
One last thing to try - to possibly narrow down the problem - what happens if you don't set that query cache to expire? 

-Tom

On 10/04/2012 5:51 AM, Tim Martin wrote:

I can put a breakpoint in just before calling and I can see the settings in the
query


On 29/03/2012 19:06, Tom Ware wrote:

Hi Tim,

  A couple of us here have had a look at this and the only thing we can think
of is that somehow you have overridden the query somewhere else (in another
class or in xml)

  Can you check the query before you run it?  You should be able to use our
JpaHelper class.  JpaHelper.getDatabaseQuery(query).  You'll get a
ReadAllQuery back.  Take a look at the result of
getQueryResultsCachePolicy().  It should be a QueryResultsCachePolicy. Make
sure your settings seem to be there.

-Tom

On 29/03/2012 10:23 AM, Tim Martin wrote:

at the point below it had run a 100 or so times - never hitting the cache, there
are only about 10 distinct values for the "name" parameter... prob all within
few seconds

[3/26/12 12:06:01:919 UTC] 00000083 SystemOut O Query Monitor:1332763561784
Query Cache Database
biz.wss.interfaces.entities.bo.ssi.OurAccount-OurAccount.findByCoreId 35 97
biz.wss.server.interfaces.workflow.ControllerPath-ControllerPath.findByName 0
138

On 29/03/2012 14:36, Tom Ware wrote:

Hi Tim,

I quickly tried to reproduce your problem and have not been able to do so with
a simple test case.

How many times does your ControllerPath.findByName query get run? How often is
it run with the same "name" parameter? How much time is there between calls
with the same name?

I assume name is mapped as a Basic?

-Tom


On 27/03/2012 10:35 AM, Tim Martin wrote:




On 26/03/2012 8:15 AM, Tim Martin wrote:

I've placed the hints on the NamedQuery declaration - but it's still not
working
as I would expect :

eg here
...OurAccount.findByCoreId - hits the cache
but ...ControllerPath.findByName never does

it seems the field
org.eclipse.persistence.queries.ReadQuery.temporaryCachedQueryResults
does not
always get populated (never gets populated in our controller path case) ?
so when it comes to put the results in the cache from there ( in
org.eclipse.persistence.queries.ReadQuery.clonedQueryExecutionComplete(DatabaseQuery,



AbstractSession) it fails to do so.....

I have tried getting the call for ControllerPath.findByName to be
inside/outside
a transaction but it appears to make no difference ?

[3/26/12 12:06:01:919 UTC] 00000083 SystemOut O Query Monitor:1332763561784
Query Cache Database
biz.wss.interfaces.entities.bo.ssi.OurAccount-OurAccount.findByCoreId 35 97
biz.wss.server.interfaces.workflow.ControllerPath-ControllerPath.findByName
0 138

@NamedQuery(name=NamedQueryNames.CONTROLLERPATH_FINDBYNAME,
query="SELECT cp FROM ControllerPath cp "+
" WHERE cp.name = :name",
hints = {@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE, value = "True"),
@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE_RANDOMIZE_EXPIRY,
value = "true"),
@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE_SIZE, value = "103"),
@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE_EXPIRY, value =
"600000")})


@NamedQuery(name=NamedQueryNames.OURACCOUNT_FIND_BY_COREID,
query="SELECT t FROM OurAccount t "+
"WHERE t.accountId = :accountId",
hints = {@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE, value = "True"),
@QueryHint(name
=QueryHints.QUERY_RESULTS_CACHE_RANDOMIZE_EXPIRY, value = "true"),
@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE_SIZE, value =
"503"),
@QueryHint(name =QueryHints.QUERY_RESULTS_CACHE_EXPIRY, value =
"300000")})

@Override
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public ControllerPath findControllerPath(String name) {


Query query =
em.createNamedQuery(NamedQueryNames.CONTROLLERPATH_FINDBYNAME);
query.setParameter("name", name);
try {

ControllerPath cp = (ControllerPath)query.getSingleResult();
if (log.isDebugEnabled())
log.debug("findControllerPath("+name+") returning:"+cp);
return cp;

} catch (NoResultException nrx) {
log.error("No ControllerPath configured for name:"+name);
} catch (NonUniqueResultException nux) {
log.error("Multiple ControllerPaths configured for name:"+name);
}
return null;
}

@Override
public OurAccount getOurAccountByCoreId(String accountId) {

if(accountId == null || accountId.length() == 0)
return null;

try {
Query q= em.createNamedQuery(NamedQueryNames.OURACCOUNT_FIND_BY_COREID)
.setParameter("accountId", accountId);


return (OurAccount) q.getSingleResult();
}
catch (NoResultException e) {
return null;
}

}

both queries are fairly simple
On 16/03/2012 13:29, Tom Ware wrote:

Hi Tim,

Whether a query is unprepared when a hint is added to them depends on the
hint. For the query cache hint, you are correct, it causes the query to be
unprepared.

Query Hints are portable. i.e. If you specify a query hint on a query and
your provider doesn't support it, it should just be ignored.

-Tom

On 16/03/2012 5:50 AM, Tim Martin wrote:

ok- that seems the right assumption - setting the query hint clears the
"isPrepared" for the query -
can you confirm :
that is what you expect to happen
that we must define the hint on the name query when it is created eg - the
only
problem there is that we then have eclipselink specific code hardcoded - I
guess
we can add a level of redirection (yee ha) to have a constant that we swap
if we
swap jpa providers to make it a little easier to swallow....

@NamedQueries({
@NamedQuery(name=NamedQueryNames.THEIRSSI_IDONLY_FIND_BY_CORE_TRAN,
query="SELECT t FROM TheirSsiIdOnly t " +
"WHERE t.sourceId = :tran",hints = {@QueryHint(name
="eclipselink.query-results-cache", value = "True"),
@QueryHint(name
="eclipselink.query-results-cache.randomize-expiry", value = "true"),
@QueryHint(name
="eclipselink.query-results-cache.size", value = "503"),
@QueryHint(name
="eclipselink.query-results-cache.expiry", value = "300000")})


Daemon Thread [SIBJMSRAThreadPool : 6] (Suspended (breakpoint at line
1988 in
org.eclipse.persistence.queries.DatabaseQuery))
owns: com.ibm.ws.sib.ra.inbound.impl.SibRaSingleMessageDeletionDispatcher
(id=2840)
owns: com.ibm.ws.sib.ra.inbound.impl.SibRaSingleProcessListener$SibRaWork
(id=2788)

org.eclipse.persistence.queries.ReadAllQuery(org.eclipse.persistence.queries.DatabaseQuery).setIsPrepared(boolean)




line: 1988

org.eclipse.persistence.queries.ReadAllQuery(org.eclipse.persistence.queries.ObjectLevelReadQuery).setIsPrepared(boolean)




line: 1911

org.eclipse.persistence.queries.ReadAllQuery(org.eclipse.persistence.queries.ReadQuery).setQueryResultsCachePolicy(org.eclipse.persistence.queries.QueryResultsCachePolicy)




line: 352

org.eclipse.persistence.queries.ReadAllQuery(org.eclipse.persistence.queries.ReadQuery).cacheQueryResults()




line: 92

org.eclipse.persistence.internal.jpa.QueryHintsHandler$QueryCacheHint.applyToDatabaseQuery(java.lang.Object,




org.eclipse.persistence.queries.DatabaseQuery, java.lang.ClassLoader,
org.eclipse.persistence.internal.sessions.AbstractSession) line: 1072

org.eclipse.persistence.internal.jpa.QueryHintsHandler$QueryCacheHint(org.eclipse.persistence.internal.jpa.QueryHintsHandler$Hint).apply(java.lang.Object,




boolean, org.eclipse.persistence.queries.DatabaseQuery,
java.lang.ClassLoader,
org.eclipse.persistence.internal.sessions.AbstractSession) line: 361

org.eclipse.persistence.internal.jpa.QueryHintsHandler$Hint.apply(java.lang.String,




boolean, java.lang.Object, org.eclipse.persistence.queries.DatabaseQuery,
java.lang.ClassLoader,
org.eclipse.persistence.internal.sessions.AbstractSession) line: 339

org.eclipse.persistence.internal.jpa.QueryHintsHandler.apply(java.lang.String,


java.lang.Object, org.eclipse.persistence.queries.DatabaseQuery,
java.lang.ClassLoader,
org.eclipse.persistence.internal.sessions.AbstractSession) line: 171

org.eclipse.persistence.internal.jpa.EJBQueryImpl<X>.setHintInternal(java.lang.String,




java.lang.Object) line: 1018

org.eclipse.persistence.internal.jpa.EJBQueryImpl<X>.setHint(java.lang.String,


java.lang.Object) line: 1000

org.eclipse.persistence.internal.jpa.EJBQueryImpl<X>.setHint(java.lang.String,


java.lang.Object) line: 81

biz.wss.jpa.impl.EclipselinkQueryHints(biz.wss.jpa.impl.BaseQueryHints).setHint(javax.persistence.Query,




java.lang.String, java.lang.Object) line: 59

biz.wss.jpa.impl.EclipselinkQueryHints.setQueryResultCacheHint(javax.persistence.Query,




int, int) line: 64
biz.wss.jpa.JPAType.setQueryResultCacheHint(javax.persistence.Query, int,
int) line: 89

biz.wss.server.services.local.bo.ssi.TheirSSIDAOEJB.getTheirSsiIdBySourceId(java.lang.String)




line: 163



On 16/03/2012 09:33, Tim Martin wrote:

looking at the javadoc for isPrepared()

"INTERNAL: Queries are prepared when they are executed and then do not
need to
be prepared on subsequent executions. This method returns true if this
query
has been prepared. Updating the settings on a query will 'un-prepare' the
query."

does that mean that when applying the query results cache hints to the
query
after calling em.createQuery it is updateing the settings and un
preparing
them ?

we get and execute the query like so :

private TheirSsiIdOnly getTheirSsiIdBySourceId(String tran) {

if(tran == null || tran.length() == 0)
return null;

try {
Query q = em.createNamedQuery(
NamedQueryNames.THEIRSSI_IDONLY_FIND_BY_CORE_TRAN )
.setParameter("tran", tran);

jpaType.setQueryResultCacheHint(q, 503, 1000*60*5); <<-- sets the
query result cache hint, size and timeout values

return (TheirSsiIdOnly) q.getSingleResult();
}
catch (NoResultException e) {
return null;
}

}

On 16/03/2012 09:29, Tim Martin wrote:

the sql show up in the log, but after debugging thru the code it
seems its
failing to behave as expected when this is called as "ifPrepared()"
always
returns false ?
It's just a standard named query...


@NamedQueries({
@NamedQuery(name=NamedQueryNames.THEIRSSI_IDONLY_FIND_BY_CORE_TRAN,
query="SELECT t FROM TheirSsiIdOnly t " +
"WHERE t.sourceId = :tran")

org.eclipse.persistence.queries.ReadAllQuery.execute(AbstractSession,
AbstractRecord) verion 2.3.3.m1

@Override
public Object execute(AbstractSession session, AbstractRecord row)
throws
DatabaseException {
if (shouldCacheQueryResults()) {
if (getContainerPolicy().overridesRead()) {
throw QueryException.cannotCacheCursorResultsOnQuery(this);
}
if (shouldConformResultsInUnitOfWork()) {
throw QueryException.cannotConformAndCacheQueryResults(this);
}
_/*if (isPrepared()) {// only prepared queries can have cached
results.*/_
Object queryResults = getQueryResults(session, row, true);
if (queryResults != null) {
if (QueryMonitor.shouldMonitor()) {
QueryMonitor.incrementReadAllHits(this);


queryResultCachingPolicy QueryResultsCachePolicy
QueryResultsCachePolicy (id=1803) QueryResultsCachePolicy
cacheType Class<T> Class<T>
(org.eclipse.persistence.internal.identitymaps.CacheIdentityMap)
(id=3360)
Class<T>
invalidationPolicy CacheInvalidationPolicy
TimeToLiveCacheInvalidationPolicy (id=1600)
TimeToLiveCacheInvalidationPolicy
NO_EXPIRY long -1 long
isInvalidationRandomized boolean false boolean
random Random null null
shouldRefreshInvalidObjectsOnClone boolean true boolean
shouldUpdateReadTimeOnUpdate boolean false boolean
timeToLive long 300000 long
isNullIgnored boolean false boolean
maximumResultSets int 503 int

Daemon Thread [SIBJMSRAThreadPool : 4] (Suspended (breakpoint at line
361 in
org.eclipse.persistence.queries.ReadAllQuery))
owns:
com.ibm.ws.sib.ra.inbound.impl.SibRaSingleMessageDeletionDispatcher
(id=1385)
owns:
com.ibm.ws.sib.ra.inbound.impl.SibRaSingleProcessListener$SibRaWork
(id=1790)

org.eclipse.persistence.queries.ReadAllQuery.execute(org.eclipse.persistence.internal.sessions.AbstractSession,




org.eclipse.persistence.internal.sessions.AbstractRecord) line: 361

org.eclipse.persistence.queries.ReadAllQuery(org.eclipse.persistence.queries.ObjectLevelReadQuery).executeInUnitOfWork(org.eclipse.persistence.internal.sessions.UnitOfWorkImpl,




org.eclipse.persistence.internal.sessions.AbstractRecord) line: 1128

org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork(org.eclipse.persistence.internal.sessions.UnitOfWorkImpl).internalExecuteQuery(org.eclipse.persistence.queries.DatabaseQuery,




org.eclipse.persistence.internal.sessions.AbstractRecord) line: 2871

org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork(org.eclipse.persistence.internal.sessions.AbstractSession).executeQuery(org.eclipse.persistence.queries.DatabaseQuery,




org.eclipse.persistence.internal.sessions.AbstractRecord, int) line:
1516

org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork(org.eclipse.persistence.internal.sessions.AbstractSession).executeQuery(org.eclipse.persistence.queries.DatabaseQuery,




org.eclipse.persistence.internal.sessions.AbstractRecord) line: 1498

org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork(org.eclipse.persistence.internal.sessions.AbstractSession).executeQuery(org.eclipse.persistence.queries.DatabaseQuery,




java.util.List) line: 1463
org.eclipse.persistence.internal.jpa.EJBQueryImpl<X>.executeReadQuery()
line: 485
org.eclipse.persistence.internal.jpa.EJBQueryImpl<X>.getSingleResult()
line: 773

biz.wss.server.services.local.bo.ssi.TheirSSIDAOEJB.getTheirSsiIdBySourceId(java.lang.String)




line: 165

biz.wss.server.services.local.bo.ssi.TheirSSIDAOEJB.getTheirInstructionByCoreTran(java.util.List<java.lang.String>)




line: 182


--
[3/16/12 8:55:44:159 UTC] 00000068 SystemOut O [EL Fine]: 2012-03-16
08:55:44.158--ServerSession(1818651750)--Connection(455416613)--Thread(Thread[SIBJMSRAThreadPool




: 5,5,main])--SELECT ID, SOURCEID FROM WSSDBA.WBR_SD_THEIRSSI WHERE
(SOURCEID
= ?)
bind => [000810587000000]
--
[3/16/12 8:55:44:210 UTC] 00000068 SystemOut O [EL Fine]: 2012-03-16
08:55:44.21--ServerSession(1818651750)--Connection(1297435989)--Thread(Thread[SIBJMSRAThreadPool




: 5,5,main])--SELECT ID, SOURCEID FROM WSSDBA.WBR_SD_THEIRSSI WHERE
(SOURCEID
= ?)
bind => [000810587000000]


On 14/03/2012 17:21, Tom Ware wrote:

When you run the query with logging turned on, do you see SQL?

On 14/03/2012 11:54 AM, Tim Martin wrote:

Tom - these query cache hits should show up in the QueryMonitor
shouldnt
they ?
the ...getBaseCurrencyPairByArea below is (should) be using the
query-results-cache hint....

the hint is applied to the query returned from
em.createQuery(namedQueryName)



[3/14/12 15:48:14:205 UTC] 0000007a SystemOut O Query
Monitor:1331740094203
Query Cache Database
biz.wss.interfaces.entities.area.bookarea.BookArea-findByPrimaryKey
122858 11
biz.wss.interfaces.entities.currency.CurrencyImpl-findByPrimaryKey
596188 13
_/*biz.wss.interfaces.entities.currency.CurrencyPairImpl-CurrencyPair.getBaseCurrencyPairByArea





0 14318*/_
biz.wss.interfaces.entities.currency.CurrencyPairImpl-findAll 0
32899
biz.wss.interfaces.entities.currency.CurrencyPairImpl-findByPrimaryKey
419774
364775


On 12/03/2012 18:14, Tom Ware wrote:

sorry. I misunderstood the question.

To clear the full set of query results you'll have to call some
EclipseLink
native API - i.e. there is no JPA hint.

Unwrap the query using JpaHelper.getDatabaseQuery(Query), cast it
to a
org.eclipse.persistence.queries.ReadQuery and call:
clearQueryResults(AbstractSession session). You can get the session
using
the
JpaHelper API as well.

-Tom

On 12/03/2012 11:56 AM, Tim Martin wrote:

mmm, but I think that only refreshes the results for the returned
objects ? Is
that a correct assumption ?

it wouldnt remove all results for all param values from the cache
for
that named
query - meaning that the caller thats creating the named query would
need to
know to force a refresh - but it doesnt know as its not responsible
for
doing
the updates...

our current scenario is :
static data updated generically in one ejb

when real business processing logic needs to get a piece of static
data
it's
read by entity specific dao's

when a static data entity is updated I want to clear the caches (I'm
happy to
completely clear all caches at this point as it's very infrequent).
so I was hoping to call a short helper function that would do that -
initially
by calling something like this here which would invalidate
everything

genericUpdateDao.clearQueryCaches(){
List<String> qNames = Helper.getCachedQueryNames();
for each qName {
Query q = em.createQuery(qName)
q.query.setHint("eclipselink.query-results-cache.size",1)
q.getResultList();
}



On 12/03/2012 15:38, Tom Ware wrote:

I suggest you use the refresh query hint. That should refresh the
cache.

On 12/03/2012 11:09 AM, Tim Martin wrote:

Is there anyway to force a cache clear - I can see that

query.setHint(""eclipselink.query-results-cache.size",1)

and then calling the query with parameters that would never
return a
result
would clear the cache, BUT is there a better way ?


On 12/03/2012 13:12, Tom Ware wrote:

The query cache will not know to refresh when a different
persistence
operation changes one of the results held in the cache.

-Tom

On 12/03/2012 4:58 AM, Tim Martin wrote:

Tom - one further question re queryResults caching -

if I run queryA which returns object X,Y,Z which are cached
and then and update one of those objects, does the query results
cache
know or
get told enough to clear the results for queryA so re running it
will
go to
the
database instead of returning a stale result ?

thanks

http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_%28ELUG%29#How_to_Cache_Query_Results_in_the_Query_CacheHow








to Cache Query Results in the Query Cache

In addition to EclipseLink's object cache, EclipseLink also
supports
a query
cache. There is the following distinction between the two:

* The /object cache/ indexes objects by their primary key,
allowing
primary
key queries to obtain cache hits. By using the object cache,
queries
that
access the data source can avoid the cost of building the
objects
and
their
relationships if the object is already present.
* The /query cache/ is distinct from the object cache. The query
cache is
indexed by the query and the query parameters–not the object's
primary
key.
This allows for any query executed with the same parameters to
obtain a
query cache hit and return the same result set.



On 09/03/2012 14:14, Tom Ware wrote:

The hint can be added to all queries. Obviously it will improve
performance
more for queries that are executed more often. (named queries
are a
good
candidate here)

Queries are actually cloned before we apply query hints to
them,
so the
SessionCustomizer option listed below may be what you want for
certain
queries. (since it will affect the query every time it is
looked up
rather
than just the instance you get from em.createQuery()).


BTW: Some doc:

http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#How_to_Cache_Query_Results_in_the_Query_Cache










-Tom

On 09/03/2012 8:49 AM, Tim Martin wrote:

Just to check - the hint can only be added to named ? not
those
built on
the fly
(which of course we are doing are best to avoid :)



On 09/03/2012 13:45, Tom Ware wrote:

There is no setting to do this universally for a whole
persistence
unit. It
should be possible to do this using EclipseLink-native API
in a
SessionCustomizer by iterating through the queries defined
in the
query
manager and calling the required native API to set it up. The
timeouts
could
be set in the same code.

The reason there is no universal setting is that it is not
clear
that
this
would be desirable in most use-cases. EclipseLink already
has a
cache
that
provides quite a bit of performance boost and the query
results
cache is
intended to provide an additional performance boost for
selected
queries.
Using the query cache requires an understanding about the
degree
to which
results may be stale when they come back.

-Tom

On 09/03/2012 8:16 AM, Tim Martin wrote:

Hi - anyone know if there is a valid property to use in the
persistence.xml to
cache all query results by name and parameter values.
So rather than putting a hint on the queries we can do
something
like

<property name="eclipselink.query-results-cache"
value="true"/>


I've come across it searching in google - but not come
across
anything
defintive...

If so - how would we configure expiration of the cache ?
something
simple
like
expire everything every x minutes would do to start with..

Thanks
Tim









_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users



_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users




_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top