|View Issue Details|
|Type||Category||Severity||Reproducibility||Date Submitted||Last Update|
|defect||[Openbravo ERP] A. Platform||major||have not tried||2016-02-11 13:25||2017-09-21 16:49|
|Priority||normal||Resolution||fixed||Fixed in Version||3.0PR17Q4|
|Status||closed||Fix in branch||Fixed in SCM revision||7107f4841d8d|
|OS Version||Database version||Ant version|
|Product Version||SCM revision|
|Review Assigned To||aferraz|
|Regression introduced in release|
|Regression introduced by commit|
|Triggers an Emergency Pack||No|
0032205: Deleting a line on the order line window takes long on environment with big amount of data
|Description||Deleting 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 Solution||Un 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:|
|Tags||No tags attached.|
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.
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.
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
Updated to minor and deleted resolution time.
A patch will be provided to the client.
|Attached patch provided to the customer.|
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.
|Waiting for customer environment.|
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
|Code review OK|
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
|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||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||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||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||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||Note Added: 0099295|
|Copyright © 2000 - 2009 MantisBT Group|