Can JBossCMP generate SELECT statements with JOIN instead o

[tux2]

Hi!
I recognized the following:
JBoss doesn't generate sql statements using joins for 1:N Relations. The select statement uses list of OR operators wich can aggregate to an endless long statement. This is really bad sql code, because many databases can't deal with such a huge amount or OR's.
Here's an example. I want to find 1 Beleg with all it's 10 Belegposition's and that's what JBossCMP generated (from the logs):

Executing SQL: SELECT t0_b.belege_oid FROM BELEGE t0_b WHERE t0_b.belegNr = ?

Executing SQL: SELECT belegNr, kundenName FROM BELEGE WHERE (belege_oid=?)

Executing SQL: SELECT belegNr, kundenName FROM BELEGE WHERE (belege_oid=?)

Executing SQL: SELECT belegpositionen_oid FROM BELEGPOSITIONEN WHERE (BelegEB_belegPositionenEB=?)

Executing SQL: SELECT belegpositionen_oid,artikelnr, artikelbez, artikelpreis FROM BELEGPOSITIONEN WHERE (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?) OR (belegpositionen_oid=?)

Is there a way to tell JBossCMP to generate the last statement like:
SELECT belegpositionen_oid,artikelnr, artikelbez, artikelpreis FROM BELEGPOSITIONEN WHERE BelegEB_belegPositionenEB=belege_oid

If this isn't possible, is than true, that for assuming 1000 Belegposition's, JBossCMP generates a statement with 1000(!!) OR's?

Thanks
Stefan

[chavan]

Can you paste the EJB QL that you used here ?
thnx
Chavan

[morphace]

Hi,

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.

Markus