Can you paste the EJB QL that you used here ?
JBossCMP uses a read-ahead mechanism (for 1:N relationships) to minimize the amount of SQL statements issued to load the data for the entities.
This is done by collecting the primary-key values (returned by a finder) of the 1 side of the relationship.
Whenever an entity needs to be loaded, JBossCMP looks, if this primary exists in any of these collections. If yes, all the data of the corresponding keys in the collection are loaded in the preload cache.
This is done by generating these SQL-statments with such many OR's.
But believe me: this is VERY efficient due to the fact that the data are retrieved by the primary key.
You can influence the amount of OR's generated by setting the <page-size> under <read-ahead> in standardjbosscmp-jdbc.xml (even per entity, if you want).
For my database (Oracle) I use the value 500.
This all is explained in the JbossCMP docs, which I recommend.
Of course, we could use joins for this purpose, but depending of the WHERE clause of the initial finder's query, this could get very inefficient.
The only thing, that is missing now, is ability of JBossCMP to use read-ahead for N:1 relationships too.
THIS IS MISSING DAIN!! ;-)
In my case, I had to optimize things by creating some database views for the N:1 relationships.
I hope, that I could help you.