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 |
Scott Lopez 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 #1046991 is a reply to message #902050] |
Mon, 22 April 2013 16:43 |
Michael Hoennig 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 |
Michael Hoennig 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
|
|
| | |
Goto Forum:
Current Time: Wed Sep 25 13:56:42 GMT 2024
Powered by FUDForum. Page generated in 0.04372 seconds
|