openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Logemann ...@logemann.org>
Subject Re: inefficient mapping
Date Wed, 08 Apr 2009 09:09:11 GMT
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
>>
>>
>>
>>
>>
>>
>


Mime
View raw message