Hi,
the queries are not slow. There are just a hell lot of queries but you
are right. THe problem is that i ve done EAGER fetching quite
extensively and it makes no sense to load all positions when i just
want to show a list of orders in the first place. So i really need to
invest some more brain into fetch-groups or making more classes
transactional than just my DAOs.
Unfortantely i have problems with fetch-groups and detachment
currently. So things are crazy these days :)
It seems it was time that i get into problems so that i really dig
into the workings of OpenJPA. Good in the long run. Thx for your
support.
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Am 08.04.2009 um 20:03 schrieb Paul Copeland:
Marc -
If all the join columns are indexed it should not be too slow. You
could put the same SQL into an "explain" analysis tool to
investigate it. For a start run the SQL outside JPA and see how
many rows are returned.
Notice you are getting an orderpos, an order, a boxes, and two
adresses in every row with these EAGER fetches (we don't see how
Address and Boxes are mapped). At some point the memory
requirements might be an issue. If the ResultSets are large and you
need all this in memory you might want to look into
openjpa.jdbc.LRSSize.
- Paul
On 4/8/2009 2:09 AM, Marc Logemann wrote:
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
