[JBoss-dev] Avoiding big numbers of selects loading EJB cache

[Paul Hammond]

Hi guys,   Just new to this list and JBoss.   My question centers around efficient loading of EJBs into JBoss's EJB cache.   Normally if you have a finder method, findByXXXYYYZZZ etc, which returns an Enumeration of the EJBs in question, you have a corresponding ejbFindByXXXYYYZZZ method that returns an Enumeration of primary key objects for that particular EJB.   My understanding is that the implementation of the findByXXXYYYZZZ method by the container will generally call findByPrimaryKey with the Primary key objects that are returned by ejbFindByXXXYYYZZ one by one to load each EJB if it's not already in the cache. This typically involves an individual select statement from the database etc for each EJB to be loaded.   If the cache is empty on startup, and you have a finder method returning say 5000 objects, then that will generate 5000 individual selects to the database, not very efficient. I have seen a third party trading system done using BMP on WebLogic whereby initially they have to load up a portfolio of 100,000 trades to run a Risk report on them. Takes about 30 minutes plus to start it up. Not nice.   Really what you want is one select statement to get all the relevant information. How do you fit that into the EJB container architecture though?   I know O/R tools like TopLink can integrate with say WebLogic so that WebLogic uses TopLink as it's cache. TopLink does support I believe instantion of lots of objects with one query. So you can in effect quickly load the cache. Therefore the container will find all the objects for the primary key are already in the cache and won't call findByPrimaryKey, at least that is my understanding of it, please correct me if I'm wrong here.   Now in relation to JBoss, and leaving aside JAWS for a moment because supposing I want to use BMP, how would I do this with JBoss if I didn't want to use a third party O/R tool. You don't want to break the container contract obviously but I can't see how you would avoid doing what the container would do.   Here's the sequence of events I would see happening in this hypothetical ejbFindByXXX method   Method findByXXX called. Delegated to ejbFindByXXX Select query to database. Iterate through results set. Get the primary key from each row Check if it's in the cache. If it is not, create a new EJB and populate it from the result set and insert it into the cache Regardless, add the EJB primary key to the enumeration Return the enumeration   Now the container generated method would do pretty much the same thing, but will always find the EJB in the cache because we've put it there, and hence will never call findByPrimaryKey and generate loads of select statements.   However we would be messing with the containers cache which is completely outside the contract and we would be duplicating what the container does ourselves, so it's not a satisfactory way of doing things.   Is there a legit way of doing this, or is it simply not possible to avoid all those calls to findByPrimaryKey and the associated select statements.   Would JAWS CMP do it efficiently?   Regs,   Paul