Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0032205
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2016-02-11 13:252017-09-21 16:49
ReporteregoitzView Statuspublic 
Assigned Tocollazoandy4 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q4
StatusclosedFix in branchFixed in SCM revision7107f4841d8d
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0032205: 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)
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on feature request 0032282 scheduledAtulOpenbravo WhiteList / Ignore Database Validation for Foreign Key constraint in database validation for C_OrderLine columns 
depends on feature request 0032211 closedcaristu support for partial indexes 
caused by feature request 0023008 closednaiaramartinez Not working correctly when processing an order that has a not stocked BOM product 
related to defect 0032677 closedcollazoandy4 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.

-  Notes
(0084127)
alostale (manager)
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 (manager)
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 (manager)
2016-03-03 18:55

 Updated to minor and deleted resolution time.
A patch will be provided to the client.
(0085670)
aferraz (manager)
2016-04-15 14:16

 Attached patch provided to the customer.
(0088088)
marvintm (developer)
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 (manager)
2017-07-27 12:39

 Waiting for customer environment.
(0098369)
hgbot (developer)
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 (manager)
2017-08-03 14:00

 Code review OK
(0099295)
hudsonbot (developer)
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

- Issue History
Date Modified Username Field Change
2016-02-11 13:25 egoitz New Issue
2016-02-11 13:25 egoitz Assigned To => platform
2016-02-11 13:25 egoitz Modules => Core
2016-02-11 13:25 egoitz Resolution time => 1455922800
2016-02-11 13:25 egoitz Triggers an Emergency Pack => No
2016-02-11 17:37 alostale Note Added: 0084127
2016-02-11 17:50 alostale Relationship added related to 0032211
2016-02-11 17:50 alostale Assigned To platform => Triage Finance
2016-02-12 10:27 vmromanos Relationship added caused by 0023008
2016-02-12 14:39 vmromanos Note Added: 0084157
2016-02-17 08:07 AtulOpenbravo Assigned To Triage Finance => AtulOpenbravo
2016-02-17 08:07 AtulOpenbravo Status new => scheduled
2016-02-19 08:50 AtulOpenbravo Relationship added depends on 0032282
2016-02-19 09:18 AtulOpenbravo Relationship added depends on 0032283
2016-03-03 18:55 aferraz Resolution time 1455922800 =>
2016-03-03 18:55 aferraz Note Added: 0084698
2016-03-03 18:55 aferraz Severity major => minor
2016-03-04 10:45 aferraz Relationship deleted related to 0032211
2016-03-04 10:45 aferraz Relationship added depends on 0032211
2016-04-15 11:56 egoitz Issue cloned 0032677
2016-04-15 11:56 egoitz Relationship added related to 0032677
2016-04-15 13:53 aferraz Assigned To AtulOpenbravo => Triage Finance
2016-04-15 13:53 aferraz Status scheduled => feedback
2016-04-15 13:53 aferraz Status feedback => new
2016-04-15 14:15 aferraz File Added: 32005.diff
2016-04-15 14:15 aferraz File Deleted: 32005.diff
2016-04-15 14:15 aferraz File Added: 32205.diff
2016-04-15 14:16 aferraz Note Added: 0085670
2016-06-29 19:08 marvintm Note Added: 0088088
2017-07-24 22:55 egoitz Resolution time => 1503180000
2017-07-24 22:55 egoitz Severity minor => major
2017-07-24 22:55 egoitz Proposed Solution updated
2017-07-27 12:39 aferraz Assigned To Triage Finance => egoitz
2017-07-27 12:39 aferraz Note Added: 0098245
2017-07-27 12:39 aferraz Status new => feedback
2017-08-01 16:46 collazoandy4 Assigned To egoitz => collazoandy4
2017-08-01 16:47 collazoandy4 Status feedback => scheduled
2017-08-02 08:33 aferraz File Deleted: 32205.diff
2017-08-03 13:56 hgbot Checkin
2017-08-03 13:56 hgbot Note Added: 0098369
2017-08-03 13:56 hgbot Status scheduled => resolved
2017-08-03 13:56 hgbot Resolution open => fixed
2017-08-03 13:56 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7107f4841d8d3d737f8ef4a1d5b0f78f854d6da3 [^]
2017-08-03 14:00 aferraz Review Assigned To => aferraz
2017-08-03 14:00 aferraz Note Added: 0098372
2017-08-03 14:00 aferraz Status resolved => closed
2017-08-03 14:00 aferraz Fixed in Version => 3.0PR17Q4
2017-09-21 16:49 hudsonbot Checkin
2017-09-21 16:49 hudsonbot Note Added: 0099295

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker