Help: way too many JDBC calls...

[wtff]

"wtff" wrote:

"wtff" wrote:
Hello,

I got quite a big problem with CMP:
I switched the logging-level for org.jboss.ejb.plugins to DEBUG and noticed that
for fetching *one* cmp-record, jboss issues *several* queries, almost for every CMP field.
This cannot be true. I'm not a J2EE veteran but I cannot imagine that this can be right.
However, neither playing around with fetch-groups not with transaction settings
led to a better behaviour. Currently, the loading of one cmp-record takes 450 ms and
12 JDBC calls are being made. I included a shortend version of the logging output below.
As you can see, for fetching a cmp-bean with 22 fields, 12 SQL statements are being issued.
Ten of them are exactly the same.
I tested this with jboss 3.2.2 and 3.2.1 but both versions behave the same.
Please help. What I would expect to see is that there is one select statement for fetching the primary
key and one for fetching the cmp fields. My bean has no relationships and is as simple as it could be,
no fancy settings I tried out within jboss-cmp.xml or anything of that type...

greetings
Sascha

16:29:34,070 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.RepairRequest.findByPrimaryKey]
[org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:124)]
Executing SQL: SELECT id FROM repairrequest WHERE id=?

16:29:34,132 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.RepairRequest]
[org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.execute(JDBCLoadEntityCommand.java:147)]
Executing SQL: SELECT pnr, mfr, kwd, ser, spc, flc, flh, tcc, rpo, red, rfr, reqcert, fixedrepcharge, exchoffer, warranty, ain, acn, ac_flc, ac_flh, sht, rdd, intfwd FROM repairrequest WHERE (id=?)

16:29:34,195 DEBUG // same text //
16:29:34,288 DEBUG // same text //
16:29:34,304 DEBUG // same text //
16:29:34,320 DEBUG // same text //
16:29:34,507 DEBUG // same text //
16:29:34,570 DEBUG // same text //
16:29:34,570 DEBUG // same text //
16:29:34,585 DEBUG // same text //
16:29:34,632 DEBUG // same text //
16:29:34,648 DEBUG // same text //


450 ms !!! for fetching ONE record

[nraghuram]

"nraghuram" wrote:

"nraghuram" wrote:
Depends on your settings in the jbosscmp-jdbc.xml and transaction attributes and whether you are accessing the entity in multiple transactions.
You should post these settings
raghu

[wtff]

"wtff" wrote:

"wtff" wrote:
thanks for your help, raghu. I tried to attach the jboss-cmp.xml file with this message but this does not seem to work, so I posted the file at the end of this message...

The calls are done from inside the web-container.
My bean represents a "repair-request". The record is broken up into four dependant value classes and the primary key field.

The code snippet for loading the bean is as follows:

RepairRequest repairRequest = repairRequestHome.findByPrimaryKey("some id");

repairRequest.getId();
repairRequest.getPartDetails();
repairRequest.getServiceDetails();
repairRequest.getAircraftDetails();
repairRequest.getShippingDetails();

To my understanding, this means that all calls are made from within the same transaction, since the web-container has one transaction context per request, right?

Also, since the fetch group settings dictate that all fields are to be fetched at once, I don't understand why several calls are being issued.

I'd really appriciate your help. My collegues and I are not new to J2EE but we are not experts on jboss configurations and maybe we are collectively ignorant about some of the rather trickier parts of J2EE...

-----


<?xml version='1.0' encoding='UTF-8' ?>

<!DOCTYPE jbosscmp-jdbc PUBLIC
"-//JBoss//DTD JBOSSCMP-JDBC 3.2//EN"
"http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_2.dtd">

<jbosscmp-jdbc>
<defaults>
<datasource>java:/jdbc/prod/Repair</datasource>
<datasource-mapping>mySQL</datasource-mapping>

<create-table>true</create-table>
<remove-table>false</remove-table>
<read-only>false</read-only>
<read-time-out>300000</read-time-out>
<row-locking>false</row-locking>
<pk-constraint>true</pk-constraint>
<fk-constraint>false</fk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>
<read-ahead>
<strategy>on-load</strategy>
<page-size>1000</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>
<list-cache-max>1000</list-cache-max>

<unknown-pk>
<key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>

<entity-command name="key-generator" />
</defaults>

<enterprise-beans>
<entity>
<ejb-name>RepairRequest</ejb-name>
<table-name>repairrequest</table-name>
<cmp-field>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</cmp-field>

<cmp-field>
<field-name>partDetails</field-name>
<property>
<property-name>PNR</property-name>
<column-name>pnr</column-name>
</property>
<property>
<property-name>MFR</property-name>
<column-name>mfr</column-name>
</property>
<property>
<property-name>KWD</property-name>
<column-name>kwd</column-name>
</property>
<property>
<property-name>SER</property-name>
<column-name>ser</column-name>
</property>
<property>
<property-name>SPC</property-name>
<column-name>spc</column-name>
</property>
<property>
<property-name>FLC</property-name>
<column-name>flc</column-name>
</property>
<property>
<property-name>FLH</property-name>
<column-name>flh</column-name>
</property>
<property>
<property-name>TCC</property-name>
<column-name>tcc</column-name>
</property>
</cmp-field>

<cmp-field>
<field-name>serviceDetails</field-name>
<property>
<property-name>RPO</property-name>
<column-name>rpo</column-name>
</property>
<property>
<property-name>RED</property-name>
<column-name>red</column-name>
</property>
<property>
<property-name>RFR</property-name>
<column-name>rfr</column-name>
</property>
<property>
<property-name>certificationRequested</property-name>
<column-name>reqcert</column-name>
</property>
<property>
<property-name>fixedRepairCharges</property-name>
<column-name>fixedrepcharge</column-name>
</property>
<property>
<property-name>exchangeOffer</property-name>
<column-name>exchoffer</column-name>
</property>
<property>
<property-name>warranty</property-name>
<column-name>warranty</column-name>
</property>
</cmp-field>

<cmp-field>
<field-name>aircraftDetails</field-name>
<property>
<property-name>AIN</property-name>
<column-name>ain</column-name>
</property>
<property>
<property-name>ACN</property-name>
<column-name>acn</column-name>
</property>
<property>
<property-name>FLC</property-name>
<column-name>ac_flc</column-name>
</property>
<property>
<property-name>FLH</property-name>
<column-name>ac_flh</column-name>
</property>
</cmp-field>

<cmp-field>
<field-name>shippingDetails</field-name>
<property>
<property-name>SHT</property-name>
<column-name>sht</column-name>
</property>
<property>
<property-name>RDD</property-name>
<column-name>rdd</column-name>
</property>
<property>
<property-name>internalForwarder</property-name>
<column-name>intfwd</column-name>
</property>
</cmp-field>

<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>

<entity-command name="pk-sql" />
</entity>
</enterprise-beans>

<dependent-value-classes>
<dependent-value-class>
<description>Part Details</description>
<class>com.airbus.spares.repair.model.PartDetails</class>
<property>
<property-name>PNR</property-name>
<column-name>pnr</column-name>
</property>
<property>
<property-name>MFR</property-name>
<column-name>mfr</column-name>
</property>
<property>
<property-name>KWD</property-name>
<column-name>kwd</column-name>
</property>
<property>
<property-name>SER</property-name>
<column-name>ser</column-name>
</property>
<property>
<property-name>SPC</property-name>
<column-name>spc</column-name>
</property>
<property>
<property-name>FLC</property-name>
<column-name>flc</column-name>
</property>
<property>
<property-name>FLH</property-name>
<column-name>flh</column-name>
</property>
<property>
<property-name>TCC</property-name>
<column-name>tcc</column-name>
</property>
</dependent-value-class>

<dependent-value-class>
<description>Service Details</description>
<class>com.airbus.spares.repair.model.ServiceDetails</class>
<property>
<property-name>RPO</property-name>
<column-name>rpo</column-name>
</property>
<property>
<property-name>RED</property-name>
<column-name>red</column-name>
</property>
<property>
<property-name>RFR</property-name>
<column-name>rfr</column-name>
</property>
<property>
<property-name>certificationRequested</property-name>
<column-name>reqcert</column-name>
</property>
<property>
<property-name>fixedRepairCharges</property-name>
<column-name>fixedrepcharge</column-name>
</property>
<property>
<property-name>exchangeOffer</property-name>
<column-name>exchoffer</column-name>
</property>
<property>
<property-name>warranty</property-name>
<column-name>warranty</column-name>
</property>
</dependent-value-class>

<dependent-value-class>
<description>Aircraft Details</description>
<class>com.airbus.spares.repair.model.AircraftDetails</class>
<property>
<property-name>AIN</property-name>
<column-name>ain</column-name>
</property>
<property>
<property-name>ACN</property-name>
<column-name>acn</column-name>
</property>
<property>
<property-name>FLC</property-name>
<column-name>flc</column-name>
</property>
<property>
<property-name>FLH</property-name>
<column-name>flh</column-name>
</property>
</dependent-value-class>

<dependent-value-class>
<description>Shipping Details</description>
<class>com.airbus.spares.repair.model.ShippingDetails</class>
<property>
<property-name>SHT</property-name>
<column-name>sht</column-name>
</property>
<property>
<property-name>RDD</property-name>
<column-name>rdd</column-name>
</property>
<property>
<property-name>internalForwarder</property-name>
<column-name>intfwd</column-name>
</property>
</dependent-value-class>

</dependent-value-classes>

</jbosscmp-jdbc>