Strange SQL for CMR

[milasx]

Hi,

I have a relation 1 to many between table 2 tables.
The generated SQL to fetch all the children is quite strange (and inefficient :( ) MDS_VECTOR is the many side of the relation and has PK of MV_KEY and foreign key of MV_MDS_FK.

SELECT MV_KEY,MV_ID, MV_VALUE, MV_VALUE_TYPE, MV_MDS_FK, FROM MDS_VECTOR WHERE (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) OR (MV_KEY=?) .... for each PK

Would not be better to do
SELECT MV_KEY,MV_ID, MV_VALUE, MV_VALUE_TYPE, MV_MDS_ID FROM MDS_VECTOR WHERE MV_MDS_FK = ? obviously with the appropriate value.

Thanks simone

[lafr]

This is caused by read-ahead.
Take a look at http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/

[ebasley]

milasx,

Can you explain how you can view JBOSS generated SQL ?

Thanks.

Eric

[milasx]

Thanks lafr, I will give that a go. In the mean time I did speed up the query :)

To view the SQL refer to:
http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/