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
|
