2 Replies Latest reply on Sep 9, 2013 1:03 PM by Frank Langelage

    Second level cache performance when not searching by primary key

    dpocock Newbie

      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?