Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Message-Id: <9A2E12F4-A19E-400D-9F5C-E183F037B060@logemann.org>
From: Marc Logemann <li@logemann.org>
To: users@openjpa.apache.org
In-Reply-To: <49DCE6EB.5030107@jotobjects.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v930.3)
Subject: Re: inefficient mapping
Date: Wed, 8 Apr 2009 23:39:09 +0200
References: <DF6FBB52-A051-4A80-A720-89E1F99AB1B5@logemann.org>
 <49DC2CD4.8010605@jotobjects.com>
 <532D3E60-D4F3-4ABE-B495-868F7BB4ED43@logemann.org>
 <49DCE6EB.5030107@jotobjects.com>

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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>