Second level cache performance when not searching by primary key

[dpocock]

I've got Infinispan working well as a second level cache for an EJB application in JBoss 7.1

Cache is configured to hold up to 10,000,000 objects and it appears to be caching them all and working quite well, except for one thing

When I search for an object without using the primary key (e.g. I am not using EntityManager.find()), the search is extremely slow.  There is a long pause (maybe 5 seconds) and finally a hit on the database table.  I'm guessing that Infinispan is traversing all the objects in the cache one by one before deciding to go to the database.

To be more specific, the objects have a composite key (@Embeddable) with two short strings and my query is just searching for all objects with one of those strings.

The underlying database does have a separate index on each of the columns in the composite key and a direct SQL query is quite fast.

I realise I could manually extract all the objects from SQL into a HashMap, but is there some JPA way to deal with this and tell Infinispan to index the column somehow?

[wdfink]

From my understanding the query (except findByPKey) will need to use the database (EJB specification).

Only load the entity by primary key will go to the cache.

Could you enable TRACE to see whether the query did not reach the DB direct?

[lafr]

I agree to what was said before.

But the server has to make sure that all cached objects are in sync with the database before the query is executed against the database.

But this can be influenced, have a look at http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#getFlushMode().