CMP referenced collection

[gold]

I've got 1-to-N relation from User to License CMP beans.

When requesting licenses collection from User beans Jboss performs the folloing SQL queries:
1) SELECT LICENSE_ID FROM LICENSE WHERE (OWNER_USER_ID=?)
2) SELECT LICENSE_ID, ... FROM LICENSE WHERE (LICENSE_ID=?) OR (LICENSE_ID=?) OR etc..

The problem is my DB2 8.1 hangs up performing second query in case WHERE clause consist of more then 341 OR statements.

My question is is there a way to configure JBOSS to use the query like
3) SELECT LICENSE_ID, ... FROM LICENSE WHERE (OWNER_USER_ID=?)
i.e. use one statement insted of two separate?

Any other thoughts are welcome.

[ithehorrible]

As far as I know you can't specify multiple columns as you're referring to the entire entity, so you should use:

SELECT OBJECT(lic) FROM LICENSE lic WHERE ...

[sesques]

Hi gold,

I think you can just make JBoss load the entity's one by one by setting the relation read-ahead strategy to "on-load".
Otherwise, define your own finder in which you can do exactly what you suggest.

Pascal

[sesques]

Perhaps try also to limit the page-size (within the relation read-ahead tag) : this is the max number of entities loaded in a single read. This will limit the query size BTW.

Pascal

[gold]

"sesques" wrote:
Perhaps try also to limit the page-size (within the relation read-ahead tag) : this is the max number of entities loaded in a single read. This will limit the query size BTW.

Pascal

Well, Pascal, it works. Thank you.

Actually, I added <read-ahead> section to <defa ults> section of my jbosscmp-jdbc.xml. This completelly solves such issue with DB2.

PS. Own finder is a most evident solution, but it rater a hot fix then a solid design :)

[ebrain13]

Hi Pascal,

Specifying the read-ahead in the jbosscmp-jdbc.xml fixed the problem, what actually is wrong with the query generation ?

thanks,
-vikram.

[sesques]

Hi vikram,

I'm not in the JBoss gurus confidence, but it seems that the implementation of the CMP engine is just like this.
I think the fisrt query is generated by the CMR collection handler (just querying id's) and the second by the persistence engine which collects all the CMP fields for each entity. The whole optimization here is to factorize all the entity beans in a single query using (id=?) or (id=?) and so on. But the CMP engine seems not to be aware of the originated CMR.

Actually, the best way (means the most performant) to collect "entities in relation with" is to write your own finder with the "one entity" id as parameter.

If we have some chance, the JBoss CMP guru (Alex) will post his advice here.

Pascal