Paul,
thx for pointing out that @ElementJoinColumn is definitely not needed
here. I removed them and at least everything works but unfortunately
as slow as before. My example was a simple one because i dont wanted
to show of the real one, but now since i really dont know what to do,
i must do it.
My Model ( i leave out some bits because of verbosiy):
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
int oid;
@OneToOne(cascade = CascadeType.PERSIST)
@JoinColumn(name = "invaddress_oid", unique = true, nullable =
false, updatable = false)
Address invoiceAddress;
@OneToMany(mappedBy = "order" , cascade = CascadeType.ALL, fetch
= FetchType.EAGER)
List<DeliveryAddress> deliveryAddresses;
...
@Entity
public class DeliveryAddress extends Address {
@OneToMany(mappedBy = "deliveryAddress",cascade =
CascadeType.ALL, fetch = FetchType.EAGER)
List<OrderPosition> orderPositions;
@XmlTransient
@ManyToOne(cascade = CascadeType.REFRESH)
Order order;
...
@Entity
@Table(name = "orderpos")
public class OrderPosition {
@Id
long oid;
@ManyToOne(cascade = CascadeType.REFRESH)
@JoinColumn(name = "address_oid")
DeliveryAddress deliveryAddress;
Thats it. So basically i need some backreferences that when i query
for OrderPositions, i also get the parent DeliveryAddress. The same
for DeliveryAddress because i need infos on the Order. The DB looks
straighforward with foreign keys as usual.
Now my JPA Query is trivial: "select o FROM Order o"
This produces a SQL query for each and every OrderPosition like this:
[..]
[DEBUG http-8080-1 10:45:11] | <t 270855330, conn 1018597953>
executing prepstmnt 1716186906 SELECT t1.oid, t1.jpatype, t1._version,
t1.addresstype, t1.city, t1.company, t1.country, t1.department,
t1.email, t1.firstname, t1.gender, t1.lastname, t1.middlename,
t1.phone, t1.zip, t1.postofficebox, t1.street, t1.housenr, t1.title,
t2.oid, t2._version, t2.cleared, t2.id, t2.lastused, t2.orderid,
t2.boxtype, t1.deliverymode, t3.oid, t3._version, t3.created,
t3.createdymd, t3.custnr, t3.ordernr, t4.oid, t4.jpatype, t4._version,
t4.addresstype, t4.city, t4.company, t4.country, t4.department,
t4.email, t4.firstname, t4.gender, t4.lastname, t4.middlename,
t4.phone, t4.zip, t4.postofficebox, t4.street, t4.housenr, t4.title,
t4.box_oid, t4.deliverymode, t4.ORDER_OID, t3.iscardowner, t3.cost,
t3.currency, t3.pricing, t3.paymenttype, t3.printed, t3.printedby,
t3.totalprice FROM orderpos t0 INNER JOIN address t1 ON t0.address_oid
= t1.oid LEFT OUTER JOIN boxes t2 ON t1.box_oid = t2.oid LEFT OUTER
JOIN orders t3 ON t1.ORDER_OID = t3.oid LEFT OUTER JOIN address t4 ON
t3.invaddress_oid = t4.oid WHERE t0.oid = ? [params=(long) 37]
[DEBUG http-8080-1 10:45:11] | <t 270855330, conn 1018597953> [2 ms]
spent
[DEBUG http-8080-1 10:45:11] | <t 270855330, conn 1018597953>
executing prepstmnt 1843485325 SELECT t1.oid, t1.jpatype, t1._version,
t1.addresstype, t1.city, t1.company, t1.country, t1.department,
t1.email, t1.firstname, t1.gender, t1.lastname, t1.middlename,
t1.phone, t1.zip, t1.postofficebox, t1.street, t1.housenr, t1.title,
t2.oid, t2._version, t2.cleared, t2.id, t2.lastused, t2.orderid,
t2.boxtype, t1.deliverymode, t3.oid, t3._version, t3.created,
t3.createdymd, t3.custnr, t3.ordernr, t4.oid, t4.jpatype, t4._version,
t4.addresstype, t4.city, t4.company, t4.country, t4.department,
t4.email, t4.firstname, t4.gender, t4.lastname, t4.middlename,
t4.phone, t4.zip, t4.postofficebox, t4.street, t4.housenr, t4.title,
t4.box_oid, t4.deliverymode, t4.ORDER_OID, t3.iscardowner, t3.cost,
t3.currency, t3.pricing, t3.paymenttype, t3.printed, t3.printedby,
t3.totalprice FROM orderpos t0 INNER JOIN address t1 ON t0.address_oid
= t1.oid LEFT OUTER JOIN boxes t2 ON t1.box_oid = t2.oid LEFT OUTER
JOIN orders t3 ON t1.ORDER_OID = t3.oid LEFT OUTER JOIN address t4 ON
t3.invaddress_oid = t4.oid WHERE t0.oid = ? [params=(long) 38]
[..]
As you can imagine, having thousands or even millions of
OrderPositions some time, this gets quite ugly. Now the big question
is, why is this the case? In my test DB i have "only" 1520 postions
and even that produces a 14 seconds "wait" for the Queries being
processed.
Sorry for supplying so much text but i think you cant make it much
shorter. Right now i am quite clueless and this mapping is already in
production and each day it gets more worse grrrrr.
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Am 08.04.2009 um 06:49 schrieb Paul Copeland:
Hi Marc -
I wonder what kind of database schema that produces. Normally you
would have OneToMany on one side and ManyToOne on the other side,
with the mappedBy on the OneToMany side. This is the classical way
the foreign keys work in relational database models. I think you do
not need ElementJoinColumn as the defaults will be right (but I'm
not sure without trying it).
So it would look like this -
public class Retoure {
@OneToMany(mappedBy="retoure", cascade = CascadeType.ALL, fetch =
FetchType.EAGER)
List<RetourePosition> retourePositionList;
}
public class RetourePosition {
@ManyToOne(cascade = CascadeType.REFRESH, fetch = FetchType.EAGER)
Retoure retoure;
}
On 4/7/2009 5:14 PM, Marc Logemann wrote:
Hi,
i am getting this and because i have some severe performance
problems with our OpenJPA based app, i wanted to ask for help:
[INFO 02:06:25] Log4JLogFactory$LogAdapter.info(80) | Inefficient
mapping: You have declared that field
"de.logentis.bwh.model.RetourePosition.retoure" is mapped by
collection "de.logentis.bwh.model.Retoure.retourePositionList".
The mapping would be much more efficient if instead you map
"de.logentis.bwh.model.RetourePosition.retoure" and declare that
"de.logentis.bwh.model.Retoure.retourePositionList" is mapped by it.
My domain model:
@Entity
@Table(name = "retoure")
@VersionColumn(name = "_version")
public class Retoure {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "oid")
long oid;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@ElementJoinColumn(name = "retoure_oid", referencedColumnName =
"oid")
List<RetourePosition> retourePositionList;
---
@Entity
@VersionColumn(name = "_version")
@Table(name = "retourepos")
public class RetourePosition {
@Id
@Column(name = "oid")
@GeneratedValue(strategy = GenerationType.IDENTITY)
long oid;
@OneToOne(mappedBy = "retourePositionList", cascade =
CascadeType.REFRESH, fetch = FetchType.EAGER)
Retoure retoure;
---
Table Info: "retoure_oid" is a foreign key field in the
"retourepos" table that points to the "oid" field of the "retoure"
table.
To me this looks like a good way but the warning says something
else. I tried to change the mapping so that the actual mapping will
be done in "retoure" atrtribute and just do a "mappedBy" reference
on "retourePositionList" but then it wont work at all. When i read
the warning, i would exactly expect that this would work. So what
am i doing wrong here?
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
