Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipse Link insert/update performance issue(Eclipse link calls update after insert on persist. Causing performance issues.)
Eclipse Link insert/update performance issue [message #902013] Wed, 15 August 2012 13:51 Go to next message
Scott Lopez is currently offline Scott LopezFriend
Messages: 1
Registered: August 2012
Junior Member
Is there any configuration change that I need to make to prevent the following from happening:
INSERT INTO VPE_RFP_LI_HDR (JPA_ID, AVC, CONTR_DOC, COURT_CURR_CDE, CRE_BY, CRE_DT, CUST_BRNCH_CDE, CUST_REG_CDE, CUST_SGMNT, CUST_TYPE, DEF_CU_CDE, GCHID, MASTER_CONTR_ID, NSAPID, OPP_ID, OPP_NME, QTE_DATE, QTE_ID, QTE_LANG_CDE, QTE_NME, QTE_VER, QTE_SYS, QTE_TYPE, REQ_CONTEXT, REQ_TYPE, REV_LOC_CDE, SALES_REP_ENT_LVL, SALES_REP_VZ_ID, SUB_NASPID, TERM, UPDT_BY, UPDT_DT, VZ_PRIM_LEGL_ENT, JPA_VER) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The above is a one to one relationship, so the key used for creating the next entry does not use an update. However, after this line things go downhill along with performance.
INSERT INTO VPE_RFP (JPA_ID, CRE_BY, CRE_DT, PRICE_SCEN_ID, QTE_ID, REQ_VER, SCEN_VER, TRANS_TYPE, UPDT_BY, UPDT_DT, JPA_VER, TRN_RFP_HDR_JPA_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
INSERT INTO VPE_RFP_LI (JPA_ID, CW_FEAT_ID, CW_PROD_ITEM_ID, CRE_BY, CRE_DT, CU_OVRRD, CUST_PB_ID, FEAT_ACT_TYPE, LANG_OVRRD, LI_ID, ORD_TYPE, GCHID, PB_LINE_ID, PROD, PRODUCTACTIVITYTYPE, PROD_FEAT, QTY, SOL, UPDT_BY, UPDT_DT, VZ_LEGL_ENT_OVRRD, JPA_VER, ORIG_SITE_JPA_ID, TERM_SITE_JPA_ID, RFP_LI_ACCESS_COST_JPA_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
INSERT INTO VPE_RFP_SEL_PROMO (JPA_ID, CRE_BY, CRE_DT, PROMO_ID, UPDT_BY, UPDT_DT, JPA_VER) VALUES (?, ?, ?, ?, ?, ?, ?)

None of the parent keys are in the sql statements, even though the previous row has already been sent. So the updates follow with additional round trips to the database.
UPDATE VPE_RFP_LI SET TRN_RFP_LI_JPA_ID = ? WHERE (JPA_ID = ?)

UPDATE VPE_RFP_SEL_PROMO SET RFP_LI_SEL_PROMO_JPA_ID = ? WHERE (JPA_ID = ?)

Persistence mapping (shortened for clarity)
@Entity
@Table(name="VPE_RFP")
public class RequestForPricing extends AbstractDomainObject
{
   @JoinColumn(name="TRN_RFP_HDR_JPA_ID")
   @OneToOne(cascade = { CascadeType.PERSIST,CascadeType.REFRESH}, fetch = FetchType.LAZY)
   public RequestForPricingHeader getRequestForPricingHeader()
   
   @JoinColumn(name="TRN_RFP_LI_JPA_ID")
   @OneToMany(cascade = { CascadeType.PERSIST,CascadeType.REFRESH}, targetEntity=RequestForPricingLineItem.class, fetch = FetchType.LAZY )
   public List<RequestForPricingLineItem> getRequestForPricingLineItem()

   @JoinColumn(name="RFP_LI_QTE_SEL_PROMO_JPA_ID")
   @OneToMany(cascade = { CascadeType.PERSIST,CascadeType.REFRESH}, targetEntity=RequestForPricingQuoteLevelSelectedPromotion.class, fetch = FetchType.LAZY )
   public List<RequestForPricingQuoteLevelSelectedPromotion> getRequestForPricingQuoteLevelSelectedPromotion()
}


When many updates have to be performed this is causing a significant slowdown in persistence.

Any suggestions will be greatly appreciated.
Re: Eclipse Link insert/update performance issue [message #902050 is a reply to message #902013] Wed, 15 August 2012 16:21 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
The "TRN_RFP_LI_JPA_ID" and "RFP_LI_QTE_SEL_PROMO_JPA_ID" fields are updated later because they are controlled by the RequestForPricing entity processed seperately from the statements used for the RequestForPricingLineItem and RequestForPricingQuoteLevelSelectedPromotion entities. This is a draw back of using unidirectional oneToMany.
The preferered approach would be to use a bidirectional OneToMany/ManyToOne instead, or only have a relationship on the ManyToOne side.

Feel free to file an enhancement request to have this changed.

Best Regards,
Chris
Re: Eclipse Link insert/update performance issue [message #1046991 is a reply to message #902050] Mon, 22 April 2013 16:43 Go to previous messageGo to next message
Michael Hoennig is currently offline Michael HoennigFriend
Messages: 3
Registered: April 2013
Junior Member
We are encountering a similar problem, but our relationship is bidirectional:

Reference in the main entity CityEntity:
    @PrivateOwned
    @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
    @JoinColumn(name = "CITY_ID", referencedColumnName = "CITY_ID", insertable = true, updatable = true, nullable = false)
    @BatchFetch(BatchFetchType.IN)
    private List<CityNameEntity> names;


Reverse reference in the sub entity CityNameEntity:
    @ManyToOne(cascade = {}, fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "CITY_ID", nullable = false)
    private CityEntity city;


But after adding a new CityNameEntity to CityEntity.names, with the reverse reference (CityNameEntity.city) already set from our code, yet after the insert (which already includes the foreign key) it is additionally updated.

Any idea what should be the cause of this unneccessary SQL update?

Thanks
... Michael
Re: Eclipse Link insert/update performance issue [message #1047580 is a reply to message #1046991] Tue, 23 April 2013 12:02 Go to previous messageGo to next message
Michael Hoennig is currently offline Michael HoennigFriend
Messages: 3
Registered: April 2013
Junior Member
By the way, also setting updatable = false, nullable = false on both sides does not change anything, EL is still executing an unneccessary UPDATE after the INSERT.

But I found this workaround using a mapping customizer:
public class OurMappingCustomizer implements DescriptorCustomizer {
...
protected void customize(ClassDescriptor descriptor, DatabaseMapping mapping) {
    for (DatabaseMapping mapping : descriptor.getMappings()) {
        if (mapping.isOneToManyMapping()) {
            OneToManyMapping oneToManyMapping = (OneToManyMapping) mapping;
            if (oneToManyMapping.isCascadeRemove() && 
                oneToManyMapping.isPrivateOwned()) {
                Vector<DatabaseField> targetForeignKeyFields = oneToManyMapping.getTargetForeignKeyFields();
                if (targetForeignKeyFields.size() == 1 && 
                    !targetForeignKeyFields.get(0).isUpdatable() && !targetForeignKeyFields.get(0).isNullable()) {
                    oneToManyMapping.setCustomAddTargetQuery(EMPTY_UPDATE_QUERY);
                }
        ...
        }
    }
}


As we don't ever need the update after the insert because we always set the reverse reference by ourself (the database has non deferrable not null constraints on the foreign keys), I just suppress the update query using setCustomAddTargetQuery. Actually we would not even need the isUpdateable()/isNullable() condition, but this gives a bit more control about the workaround.

For these conditions, of course, the following is also needed in the core entity:
  @PrivateOwned
  @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
  @JoinColumn(name = "..._ID", referencedColumnName = "..._ID", insertable = true, updatable = false, nullable = false)
    private List<SubEntity> names;


It seems to work fine, but I am not sure if it has any side effects I don't know of.

p.s. the real code does not have all these 'if' in a single method, this is just ugly example code

[Updated on: Tue, 23 April 2013 12:03]

Report message to a moderator

Re: Eclipse Link insert/update performance issue [message #1047627 is a reply to message #1047580] Tue, 23 April 2013 13:01 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
With your mapping you haven't set up a bidirectional relationship, you have instead set up two unidirectional mappings that use the same "CITY_ID" join column in the CityEntity, which is a very bad idea.

Remove the @JoinColumn annotation from the OneToMany and instead mark the OneToMany as mapped by the ManyToOne city relationship. You will not need to use a customizer:
@OneToMany(cascade = CascadeType.ALL, mappedBy="city")

See http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Mapping/Relationship_Mappings/Collection_Mappings/OneToMany for information on OneToMany relationships.

Best Regards,
Chris


Re: Eclipse Link insert/update performance issue [message #1049012 is a reply to message #1047627] Thu, 25 April 2013 08:24 Go to previous message
Michael Hoennig is currently offline Michael HoennigFriend
Messages: 3
Registered: April 2013
Junior Member
Thank you very much, Chris. That fixes it. Cause for this mistake was introducing the reverse reference without changing the @OneToMany side. Unbelievable how many developers looked at that code and did not see what you saw!

Best regards
... Michael
Previous Topic:em.merge(treeOfEntities) impossible with optimistic locking
Next Topic:insert always null on embedded fields
Goto Forum:
  


Current Time: Thu Nov 27 18:40:02 GMT 2014

Powered by FUDForum. Page generated in 0.03708 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software