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...? 



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

Back to the top