Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » [JPA] @Index used in cache?(If an @Index is placed on a field will EclipseLink be able to use that field in a search of the cache)
[JPA] @Index used in cache? [message #717193] Fri, 19 August 2011 14:52 Go to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
I have an class that has two unique identitifers 'mainId' and 'otherId', both of type String, either one can be used to find(QueryMapItem.class, id) a single object. This is not a composite key case, but just a case in which two subsystems demand different approaches. Therefore only one can be annotated with @Id, let us say 'mainId', and I assume therefore that only one, 'mainId', can be the Primary Key (PK). The other can however be annotated as an @Index. If I use a JPA Query to search for objects of this class based on 'otherId' will Eclipselink be able to use the cache before it queries the database? Or does Eclipselink always go to the database for queries which are not on PKs, before it uses the cache? I ask because it seems that a Query on anything other than a PK always results in a call to the DB. (I have not yet tried with unique='true' on the Index.


The example;


@Entity
public class QueryMapItem {

@SuppressWarnings("unused") //used by JPA
@ManyToOne
private QuerySingleton singleton;

@Index(columnNames="otherId")
@Basic
private String otherId;

@Id
private String mainId;

@Version
private int version;

@Basic
private Integer property;


// Bonus empty constructor generated for JPA
protected QueryMapItem() {
super();
}

.....



@Entity@NamedQueries({
@NamedQuery(name = "uk.co.his.experiment8.model.query.QuerySingleton.uniqueMapItem", query = "SELECT o FROM QueryMapItem o WHERE o.otherId = :otherId", hints={@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase), @QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject)}),
})
public class QuerySingleton {

@Id
protected String id = "1";

@SuppressWarnings("unused")
// used by JPA
@Version
private int version;

public IMapItem getMapItem(Integer otherId, EntityManager em) {
Query q = em
.createNamedQuery("uk.co.his.experiment8.model.query.QuerySingleton.uniqueMapItem");
q.setParameter("otherId", otherId.toString());
try {
java.lang.Object r = q.getSingleResult();
return (QueryMapItem) r;
} catch (javax.persistence.NoResultException ex) {
return null;
}
}
Thanks

[Updated on: Fri, 19 August 2011 17:23]

Report message to a moderator

Re: [JPA] @Index used in cache? [message #717245 is a reply to message #717193] Fri, 19 August 2011 17:20 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
( I tried this latest with @Index(unique=true), but that did not seem to make any difference )
I checked this out with logging on and it is not going to the DB unnecessarily - i.e. once the QueryMapItems are cached. However it does seem to be taking a long time. Some of that is doing a UnitOfWork flush.

[Updated on: Fri, 19 August 2011 17:22]

Report message to a moderator

Re: [JPA] @Index used in cache? [message #717262 is a reply to message #717245] Fri, 19 August 2011 18:31 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Having first added a Cache size annotation to QueryMapItem;

@Entity
@Cache(size=150000, disableHits=false)
public class QueryMapItem {

...



Using Derby and calling getMapItem 10000 times in one Transaction ( yes I know I could do this with a different query - i'm just looking trying to understand where the time is going ) retrieving 10000 different QueryMapItems, having run a separate transaction first of the same size to 'prime' the cache gives me a transaction time of 89060 milliseconds, i.e. 8.9 milliseconds per item ( this seems high - compare for example to JDBC 61139 ms for 10000 items).

IS there anything I can do to speed this up - i'm sure I must be doing something wrong - or is that just the price to pay for Queries?
Re: [JPA] @Index used in cache? [message #717265 is a reply to message #717262] Fri, 19 August 2011 18:56 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Calling Query.setFlushMode(FlushModeType.COMMIT); brings it down to 71369 ms for a second txn following the prime with 10000 calls to getMapItem. By comparison if I am doing the same stuff with the primary key with hints just to use the cache first;

@NamedQuery(name = "uk.co.his.experiment8.model.noIdQuery.NoIdQuerySingleton.uniqueMapItem", query = "SELECT o FROM NoIdQueryMapItem o WHERE o.cachedKey = :cachedKey", hints=@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase))

I get 27265 ms.
Any further ideas, anyone?

[Updated on: Fri, 19 August 2011 19:02]

Report message to a moderator

(no subject) [message #717273 is a reply to message #717245] Fri, 19 August 2011 18:31 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Having first added a Cache size annotation to QueryMapItem;

@Entity
@Cache(size=150000, disableHits=false)
public class QueryMapItem {

...



Using Derby and calling getMapItem 10000 times in one Transaction ( yes I know I could do this with a different query - i'm just looking trying to understand where the time is going ) retrieving 10000 different QueryMapItems, having run a separate transaction first of the same size to 'prime' the cache gives me a transaction time of 89060 milliseconds, i.e. 8.9 milliseconds per item ( this seems high - compare for example to JDBC 61139 ms for 10000 items).

IS there anything I can do to speed this up - i'm sure I must be doing something wrong - or is that just the price to pay for Queries?
(no subject) [message #717274 is a reply to message #717262] Fri, 19 August 2011 18:56 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Calling Query.setFlushMode(FlushModeType.COMMIT); brings it down to 71369 ms for a second txn following the prime with 10000 calls to getMapItem.
Any further ideas, anyone?
Re: (no subject) [message #717852 is a reply to message #717274] Mon, 22 August 2011 14:24 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello,

@Index is for DDL generation to have the database use that field as an index. Identity requires that the entities be hashed in the cache on their ID, and because the field might not be unique (the unique identifier is only used for DDL generation), queries on the index field will go to the database. This ensures that if it is not unique, all results are returned.

What you are looking for is in-memory querying, which is documented here:
http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#How_to_Use_In-Memory_Queries

You might try
q.setHint("eclipselink.cache-usage", "CheckCacheThenDatabase");
with q.getSingleResult();

Best Regards,
Chris
Re: (no subject) [message #717856 is a reply to message #717852] Mon, 22 August 2011 14:37 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Hi, thanks Chris.

As you can see I am using NamedQueries;

@Entity
@NamedQueries({
@NamedQuery( name = "uk.co.his.experiment8.model.query.QuerySingleton.uniqueMapItem", query = "SELECT o FROM QueryMapItem o WHERE o.otherId = :otherId",
hints={
@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase),
@QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject)}),
})

My understanding is that this should gives rise to an InMemory query as far as possible? And indeed it does not seem to go to the db?

[Updated on: Mon, 22 August 2011 15:26]

Report message to a moderator

(no subject) [message #717862 is a reply to message #717265] Mon, 22 August 2011 14:24 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello,

@Index is for DDL generation to have the database use that field as an index. Identity requires that the entities be hashed in the cache on their ID, and because the field might not be unique (the unique identifier is only used for DDL generation), queries on the index field will go to the database. This ensures that if it is not unique, all results are returned.

What you are looking for is in-memory querying, which is documented here:
http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#How_to_Use_In-Memory_Queries

You might try
q.setHint("eclipselink.cache-usage", "CheckCacheThenDatabase");
with q.getSingleResult();

Best Regards,
Chris
Re: (no subject) [message #717891 is a reply to message #717862] Mon, 22 August 2011 16:27 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
So this NamedQuery will no be an ImMemoryQuery?
... because it is not really an InMemoryQuery ... I need to use the API?
or because of the query does not use a Primary Key?
(no subject) [message #717908 is a reply to message #717862] Mon, 22 August 2011 16:27 Go to previous messageGo to next message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
So this NamedQuery will no be an ImMemoryQuery?
... because it is not really an InMemoryQuery ... I need to use the API?
or because of the query does not use a Primary Key?
Re: (no subject) [message #720494 is a reply to message #717891] Tue, 30 August 2011 17:47 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

If you use the cache-usage hint on the query, then it should not go to the database if the object with the id is in the cache. Are you seeing otherwise?

In-memory querying is more expensive than a cache hit, as it is scanning the entire cache. You may wish to look at the new @CacheIndex feature in the trunk of EclipseLink, it will allow indexes to be defined on alternative keys in the cache.


James : Wiki : Book : Blog : Twitter
Re: (no subject) [message #726004 is a reply to message #720494] Fri, 16 September 2011 11:46 Go to previous message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Thanks for your reply.

So just to clarify I am using a NamedQuery;

@Entity
@NamedQueries({
@NamedQuery( name = "uk.co.his.experiment8.model.query.QuerySingleton.uniqueMapItem", query = "SELECT o FROM QueryMapItem o WHERE o.otherId = :otherId", 
hints={
@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase), 
@QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject)}),
})


Chris said
Quote:
"What you are looking for is in-memory querying, which is documented here:
http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#How_to_Use_In-Memory_Queries

You might try
q.setHint("eclipselink.cache-usage", "CheckCacheThenDatabase");
with q.getSingleResult();"


but I assume that a NamedQuery with a
@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase) hint is no different than using the API as outlined by Chris.

Since otherId is not a Primary Key this Query will not be able to do a cache hit, and will consequently trawl the Cache and will therefore be a an InMemory Query which may well be slower than bypassing the cache and going direct to the DB, especially if I use the @Index annotation to index the table by the otherId column.

With the new @CacheIndex feature of Eclipselink I should be able to achieve a cache-hit on otherId, which should be faster than a query on to the db. Sounds great thanks!

That about sums it up?

You said;
Quote:

"...it should not go to the database if the object with the id is in the cache. Are you seeing otherwise?"


No. It does not.


[Updated on: Fri, 16 September 2011 11:49]

Report message to a moderator

(no subject) [message #726006 is a reply to message #720494] Fri, 16 September 2011 11:46 Go to previous message
J F is currently offline J FFriend
Messages: 242
Registered: July 2009
Senior Member
Thanks for your reply.

So just to clarify I am using a NamedQuery;


@Entity
@NamedQueries({
@NamedQuery( name = "uk.co.his.experiment8.model.query.QuerySingleton.uniqueMapItem", query = "SELECT o FROM QueryMapItem o WHERE o.otherId = :otherId",
hints={
@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase),
@QueryHint(name=QueryHints.QUERY_TYPE, value=QueryType.ReadObject)}),
})


Chris said
"What you are looking for is in-memory querying, which is documented here:
http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#How_to_Use_In-Memory_Queries

You might try
q.setHint("eclipselink.cache-usage", "CheckCacheThenDatabase");
with q.getSingleResult();"

but I assume that a NamedQuery with a
@QueryHint(name=QueryHints.CACHE_USAGE, value=CacheUsage.CheckCacheThenDatabase) hint is no different than using the API as outlined by Chris.

Since otherId is not a Primary Key this Query will not be able to do a cache hit, and will consequently trawl the Cache and will therefore be a an InMemory Query which may well be slower than bypassing the cache and going direct to the DB, especially if I use the @Index annotation to index the table by the otherId column.

With the new @CacheIndex feature of Eclipselink I should be able to achieve a cache-hit on otherId, which should be faster than a query on to the db. Sounds great thanks!

That about sums it up?

You said;

"...it should not go to the database if the object with the id is in the cache. Are you seeing otherwise?"
Previous Topic:SQLServer, IDENTITY and History issue
Next Topic:(no subject)
Goto Forum:
  


Current Time: Sun Dec 21 20:22:37 GMT 2014

Powered by FUDForum. Page generated in 0.16475 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software