Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0032205Openbravo ERPA. Platformpublic2016-02-11 13:252017-09-21 16:49
Reporteregoitz 
Assigned Tocollazoandy4 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q4 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0032205: Deleting a line on the order line window takes long on environment with big amount of data
DescriptionDeleting a line on the order line window takes long on environment with big amount of data.
Steps To Reproduce-On an environment with thousends of order, invoices and shipment creare a order, a line, and then remove the line.

It takes arround 9 seconds on our environment.


Contact ECA for an environment where that is reproducible
Proposed SolutionUn possible solution should be to add partial indexes (available from PR17Q1) to the columns refereing to the c_orderline_id column on the c_orderline table:

(bom_parent_id)
(quotationline_id)
(ref_orderline_id)
Additional Information
TagsNo tags attached.
Relationships
depends on feature request 0032282 scheduled AtulOpenbravo WhiteList / Ignore Database Validation for Foreign Key constraint in database validation for C_OrderLine columns 
depends on feature request 0032211 closed caristu support for partial indexes 
caused by feature request 0023008 closed naiaramartinez Not working correctly when processing an order that has a not stocked BOM product 
related to defect 0032677 closed collazoandy4 Deleting a line on the shipment/receipt line tab takes long on environment with big amount of data 
Not all the children of this issue are yet resolved or closed.
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-02-11 13:25egoitzNew Issue
2016-02-11 13:25egoitzAssigned To => platform
2016-02-11 13:25egoitzModules => Core
2016-02-11 13:25egoitzResolution time => 1455922800
2016-02-11 13:25egoitzTriggers an Emergency Pack => No
2016-02-11 17:37alostaleNote Added: 0084127
2016-02-11 17:50alostaleRelationship addedrelated to 0032211
2016-02-11 17:50alostaleAssigned Toplatform => Triage Finance
2016-02-12 10:27vmromanosRelationship addedcaused by 0023008
2016-02-12 14:39vmromanosNote Added: 0084157
2016-02-17 08:07AtulOpenbravoAssigned ToTriage Finance => AtulOpenbravo
2016-02-17 08:07AtulOpenbravoStatusnew => scheduled
2016-02-19 08:50AtulOpenbravoRelationship addeddepends on 0032282
2016-02-19 09:18AtulOpenbravoRelationship addeddepends on 0032283
2016-03-03 18:55aferrazResolution time1455922800 =>
2016-03-03 18:55aferrazNote Added: 0084698
2016-03-03 18:55aferrazSeveritymajor => minor
2016-03-04 10:45aferrazRelationship deletedrelated to 0032211
2016-03-04 10:45aferrazRelationship addeddepends on 0032211
2016-04-15 11:56egoitzIssue cloned0032677
2016-04-15 11:56egoitzRelationship addedrelated to 0032677
2016-04-15 13:53aferrazAssigned ToAtulOpenbravo => Triage Finance
2016-04-15 13:53aferrazStatusscheduled => feedback
2016-04-15 13:53aferrazStatusfeedback => new
2016-04-15 14:15aferrazFile Added: 32005.diff
2016-04-15 14:15aferrazFile Deleted: 32005.diff
2016-04-15 14:15aferrazFile Added: 32205.diff
2016-04-15 14:16aferrazNote Added: 0085670
2016-06-29 19:08marvintmNote Added: 0088088
2017-07-24 22:55egoitzResolution time => 1503180000
2017-07-24 22:55egoitzSeverityminor => major
2017-07-24 22:55egoitzProposed Solution updated
2017-07-27 12:39aferrazAssigned ToTriage Finance => egoitz
2017-07-27 12:39aferrazNote Added: 0098245
2017-07-27 12:39aferrazStatusnew => feedback
2017-08-01 16:46collazoandy4Assigned Toegoitz => collazoandy4
2017-08-01 16:47collazoandy4Statusfeedback => scheduled
2017-08-02 08:33aferrazFile Deleted: 32205.diff
2017-08-03 13:56hgbotCheckin
2017-08-03 13:56hgbotNote Added: 0098369
2017-08-03 13:56hgbotStatusscheduled => resolved
2017-08-03 13:56hgbotResolutionopen => fixed
2017-08-03 13:56hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7107f4841d8d3d737f8ef4a1d5b0f78f854d6da3 [^]
2017-08-03 14:00aferrazReview Assigned To => aferraz
2017-08-03 14:00aferrazNote Added: 0098372
2017-08-03 14:00aferrazStatusresolved => closed
2017-08-03 14:00aferrazFixed in Version => 3.0PR17Q4
2017-09-21 16:49hudsonbotCheckin
2017-09-21 16:49hudsonbotNote Added: 0099295

Notes
(0084127)
alostale   
2016-02-11 17:37   
This environment has ~12M of rows in c_orderline.

When doing a: delete from c_orderline where c_orderline_id = ?

3 seq scans are executed in c_orderline. Consuming up to 7 seconds. Those scans are triggered by pg to keep FK constraints from c_orderline to c_orderline (c_orderline_quotation, c_orderline_soporef and c_orderline_bomparentid). Because referred columns are not indexed, a seq scan is required on deletion to ensure no other line is pointing to the one being deleted.

Possible solutions:

Both of these solutions were tested in this environment resulting in a similar performance for deletion (time went down from 7000ms to 6ms)

1. Add indexes
If these 3 columns are indexed the sequential scan is not performed anymore.

This approach, though, penalizes in:
* DB size consumed for those indexes (ie. in this instance index for bom_parent column takes ~240MB), adding the 3 indexes increases table size in 720MB
* It will also have a performance penalization when for insertion, because these new indexes need to be maintained. Being insertion much more common than deletion, this factor needs to be balanced.

2. Remove these FKs
If application warranties data consistency for those cases, it might be possible to completely remove them.
(0084157)
vmromanos   
2016-02-12 14:39   
c_orderline_soporef -> Remove FK as this field is not used/displayed
c_orderline_quotation -> Remove FK as this column seems to be not used
c_orderline_bomparentid -> Remove FK as the column's consistency is properly managed by the application
(0084698)
aferraz   
2016-03-03 18:55   
Updated to minor and deleted resolution time.
A patch will be provided to the client.
(0085670)
aferraz   
2016-04-15 14:16   
Attached patch provided to the customer.
(0088088)
marvintm   
2016-06-29 19:08   
Important remark: The foreign key c_orderline_quotation is used both in the ERP and in the Web POS for the Quotation functionality. This foreign key is the reference from an order line to a quotation line (which is also saved in this table), and it's quite important because if it's removed, then the user may mistakenly delete a quotation who is being referred from an existing sales order, which would leave the data inconsistent.

We have also seen that there is inconsistent data both in the c_orderline_soporef foreign key, and also in the c_orderline_quotation foreign key, in the customer where the foreign keys were removed, so the decision to remove these foreign keys should be reviewed, as it seems it's not correct.
(0098245)
aferraz   
2017-07-27 12:39   
Waiting for customer environment.
(0098369)
hgbot   
2017-08-03 13:56   
Repository: erp/devel/pi
Changeset: 7107f4841d8d3d737f8ef4a1d5b0f78f854d6da3
Author: Armaignac <collazoandy4 <at> gmail.com>
Date: Tue Aug 01 11:59:06 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/7107f4841d8d3d737f8ef4a1d5b0f78f854d6da3 [^]

Fixes issue 32205: Deleting a line on the order line window takes long

Three new partial indexes were created to improve the performance on a huge
enviroment.

---
M src-db/database/model/tables/C_ORDERLINE.xml
---
(0098372)
aferraz   
2017-08-03 14:00   
Code review OK
(0099295)
hudsonbot   
2017-09-21 16:49   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/9750b78d3e5c [^]
Maturity status: Test