curious SQL-Statements when loading related data

[lafr]

I have existing database tables for order and /line item.
Orders are in Table afsta represented by EB Afsta, line items are in Table afpos represented by EB Afpos.
Afsta has afstaSerial as PK, Afpos has afposSerial as PK and afstaSerial as FK.

In server.log I found this lines:
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Afsta.execute] load relation SQL: SELECT afpos_serial FROM AFPOS WHERE (afsta_serial=?)
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Afpos.execute] Executing SQL: SELECT afpos_serial, var_id FROM AFPOS WHERE (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?)
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Afpos.execute] Executing SQL: SELECT afpos_serial, tst_nr FROM AFPOS WHERE (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?)
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Afpos.execute] Executing SQL: SELECT afpos_serial, var_fam_nr FROM AFPOS WHERE (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?) OR (afpos_serial=?)

and so on.

First the keys are ascertained and then the rows should be loaded.
But every access to afpos loads only one additional field (var_id, tst_nr, var_fam_nr, ...).
At the end, the whole data is there, but this is not very fast.

Any ideas ?
Is this perhaps a bug ?

[gorano]

This is not a bug. It is the way JBoss is doing it.

Please see, http://www.jboss.org/index.html?module=bb&op=viewtopic&t=49517

/Goran

[lafr]

I think you misunderstood the problem description.
My problem is not the where clause of the statement.

The problem is, that the first access gets the PK-field plus first additional field, the second access reads the same rows, but know the PK-field plus second additional field, and so on and so on.
I expected 5 select to read the 84 rows with max 20 at a time (4x20+1x4).
Instead I get 315 selects (factor 63 because of 63 additional columns in afpos).

[sesques]

Hi,

It looks like you are playing with eager and lazy loading options.
As far I as know, nobody here has seen this problem. I use myself JBoss 3.2.3 and all the fields are loaded in one shot.
Give us more details about the JBoss version and perhaps your descriptors.

Pascal

[darranl]

Is this perhaps a bug ?

Why is everyone so obsessed with blaming any problem on a bug at the moment?

[lafr]

I think I don't play with eager and lazy loading.
I also have seen it working some time ago. Current I use JBoss 3.2.4RC2.
Deployment descriptors are generated by XDoclet 1.2.1 and are accessible at http://www.lafr.de/ejb-jar.xml
and
http://www.lafr.de/jbosscmp-jdbc.xml.
The EB Afsta ist called from SLSB CustomerOrder.

[ironbird]

Your descriptors are OK, means using default values.
Do you try on JBoss 3.2.3 ? I have seen many peculiarities for the ejb plugin on JBoss 3.2.4 series.
Perhaps try also to optimize loading. Is your problem occurs after a long run or from the start ?

[juha]

It could be a bug, however, some of your front end sessions are running with "Supports" transaction tag, so make sure you have transaction associated with the thread when you're accessing CMP entities. Try first with "Required" tag on all sessions.

[lafr]

The session beans with "Supports" are not relevant here.
Although I switched tags from "Supports" to "Required" for all beans.
The EntityBeans are called from a JSP via session bean CustomerOrder (which already has tag "Required").
I tried 3.2.3 today with my ear and here I got the expected behaviour.
The problem occurs with also with the first access.
So I think this is really a bug in 3.2.4RC2.

[lafr]

Was I dreaming last night ? I think not. I'm sure I saw a posting from alex loubiansky yesterday evening here.
He told me to put "read-ahead="on-find"" onto the relation.

I tried this with success.
Now again only one DB-Access in server.log for reading the all the rows with all the fields from the related table.

Thanks a lot.

[ironbird]

No you were not dreaming, or we were all dreaming. But it seems that the server falls down many times actually.
Its not Alex who tells you the "on-find" strategy but me.
But Alex post after me saying that you should keep your on-load strategy, I don't know why.
on-load load the beans one by one
on-find load a page size each time on bean is not in current page

Do you remove some options in your datasource file (pool size or something like this) ?

[aloubyansky]

This is the copy of my post:

I confirm that this is a change in behaviour between 3.2.3 and 3.2.4RC2.
3.2.3 has a bug in on-find read-ahead for single-object finders. I.e. when the result of a finder is a single object which is preloaded on-find with some load group which doesn't cover all the fields in the default load group (*), first access (even to the field that was preloaded) to this found instance will load the default load group anyway. Which is obviously wrong.
The difference between 3.2.3 and 3.2.4 is that if at least some data was loaded from read-ahead cache 3.2.4 does not proceed to load the default load group while 3.2.3 does.

So, in the case above, the data (CMR field) was preloaded and other CMP field access triggers lazy-loading. This is expected behaviour according to the fix. I still have to think about this.

To prevent this you can:
1) define a lazy-load group and load all the fields at once on first access with on-load read-ahead (like 3.2.3 in this case)
2) define on-find read-ahead for the relationship and preload all the data at once at CMR access. This is even better than 1).