Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027713Openbravo ERP04. Warehouse managementpublic2014-09-30 09:552014-12-30 23:23
Reporteralostale 
Assigned Toeduardo_Argal 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Topramakrishnan
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027713: slow post of goods shipment if there are many storage detail for that product
DescriptionPosting a goods shipment which has lines for a product that has many rows in m_storage_detail table is slow.
Steps To Reproduce* Use same DB defined for issue 0027712
* Creating shipments with lines for 'TEST_IMEI_01' product
* Post those shipments, the times obtained before and after the proposed solution are:
  10 lines: 4066ms -> 217ms
  100 lines: 21275 -> 622
  1000 lines: 198877 -> 10700
  10000 lines: (not tested) -> 69082
Proposed SolutionThe time is spent mostly in m_check_stock function. Note that the query is filtering m_storage_detail by product which is indexed, but in this case there are many rows for that product. It is processing all those lines just to know if there are ANY.

Proposed changes (see attached diff):
- Add indexes on QTYONHAND and QTYORDERONHAND
- Don't process all rows but just check if there is any row
- Don't do coalesce for not nullable qtyOnHand field
- Is rounding needed to check if it is less than 0? If not, remove it
Additional Information
TagsPerformance
Relationships
related to defect 0027712 closed eduardo_Argal slow goods receipt batch insertion 
related to defect 0027733 new dmiguelez M_InOut post allows modifications while the document is being processed 
related to defect 0027740 closed eduardo_Argal poor performance when completing a shipment with many lines 
causes defect 0029211 closed eduardo_Argal Cannot complete a goods receipt with a line without attribute set value if it has a related prereservation 
Attached Filesdiff issue-27713.diff (1,909) 2014-09-30 10:01
https://issues.openbravo.com/file_download.php?file_id=7282&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-09-30 09:55alostaleNew Issue
2014-09-30 09:55alostaleAssigned To => dmiguelez
2014-09-30 09:55alostaleModules => Core
2014-09-30 09:55alostaleTriggers an Emergency Pack => No
2014-09-30 09:56alostaleTag Attached: Performance
2014-09-30 09:56alostaleProposed Solution updated
2014-09-30 09:57alostaleRelationship addedrelated to 0027712
2014-09-30 09:58alostaleIssue Monitored: alostale
2014-09-30 10:00alostaleProposed Solution updated
2014-09-30 10:01alostaleFile Added: issue-27713.diff
2014-09-30 11:26jonalegriaesarteResolution time => 1414623600
2014-09-30 11:26jonalegriaesarteSeverityminor => major
2014-10-02 07:58alostaleRelationship addedrelated to 0027733
2014-10-02 12:45egoitzRelationship addedrelated to 0027740
2014-10-09 16:45jpcalventeIssue Monitored: jpcalvente
2014-10-21 18:40hgbotCheckin
2014-10-21 18:40hgbotNote Added: 0071101
2014-10-21 18:40hgbotStatusnew => resolved
2014-10-21 18:40hgbotResolutionopen => fixed
2014-10-21 18:40hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^]
2014-10-22 10:19umartirenaNote Added: 0071112
2014-10-22 10:19umartirenaStatusresolved => new
2014-10-22 10:19umartirenaResolutionfixed => open
2014-10-22 10:20umartirenaNote Edited: 0071112bug_revision_view_page.php?bugnote_id=0071112#r6873
2014-10-22 10:24hgbotCheckin
2014-10-22 10:24hgbotNote Added: 0071113
2014-10-22 10:24hgbotStatusnew => resolved
2014-10-22 10:24hgbotResolutionopen => fixed
2014-10-22 10:24hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^] => http://code.openbravo.com/erp/devel/pi/rev/5223d48811474fe528f71aa2fd33d090b19bb899 [^]
2014-10-23 18:05hgbotCheckin
2014-10-23 18:05hgbotNote Added: 0071146
2014-10-31 13:09dmitry_mezentsevAssigned Todmiguelez => Sandrahuguet
2014-10-31 13:22SandrahuguetAssigned ToSandrahuguet => eduardo_Argal
2014-11-04 06:44pramakrishnanReview Assigned To => pramakrishnan
2014-11-04 06:44pramakrishnanNote Added: 0071387
2014-11-04 06:44pramakrishnanStatusresolved => closed
2014-12-30 23:23hudsonbotCheckin
2014-12-30 23:23hudsonbotNote Added: 0072964
2014-12-30 23:23hudsonbotCheckin
2014-12-30 23:23hudsonbotNote Added: 0072968
2014-12-30 23:23hudsonbotCheckin
2014-12-30 23:23hudsonbotNote Added: 0072979
2015-03-10 09:59ngarciaRelationship addedcauses 0029211

Notes
(0071101)
hgbot   
2014-10-21 18:40   
Repository: erp/devel/pi
Changeset: c1d30f85a429d63b04ee6470072ec5b1742acc5e
Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
Date: Tue Oct 21 18:40:15 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^]

Fixes bug 27713, 27712

Adds new index for qtyonhand qtyorderonhand to storage detail table.
Changes m_attributesetinstance_id to not null for M_STORAGE_DETAIL, and M_RESERVATION_STOCK tables as almost all records where already using '0' instead of null.
Removes unnecessary coalesce

---
M src-db/database/model/functions/M_CHECK_STOCK.xml
M src-db/database/model/functions/M_UPDATE_INVENTORY.xml
M src-db/database/model/tables/M_RESERVATION_STOCK.xml
M src-db/database/model/tables/M_STORAGE_DETAIL.xml
---
(0071112)
umartirena   
2014-10-22 10:19   
(edited on: 2014-10-22 10:20)
Reopened due to an error creating m_update_inventory function in postgres 8.4
(0071113)
hgbot   
2014-10-22 10:24   
Repository: erp/devel/pi
Changeset: 5223d48811474fe528f71aa2fd33d090b19bb899
Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
Date: Wed Oct 22 10:24:27 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/5223d48811474fe528f71aa2fd33d090b19bb899 [^]

Fixes issue 27713: slow post of goods shipment if there are many storage detail for that product

Second commit required for the function to work in postgres 8.4. Parameter can not be assigned

---
M src-db/database/model/functions/M_UPDATE_INVENTORY.xml
---
(0071146)
hgbot   
2014-10-23 18:05   
Repository: erp/devel/pi
Changeset: 29b7b658c9fd67ee821a739745cd8966bfc19d56
Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
Date: Thu Oct 23 18:05:43 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/29b7b658c9fd67ee821a739745cd8966bfc19d56 [^]

Related to issue 27713. Removal of unnecessary log

---
M src-db/database/model/functions/M_CHECK_STOCK.xml
---
(0071387)
pramakrishnan   
2014-11-04 06:44   
Code Review + Testing - OK.
(0072964)
hudsonbot   
2014-12-30 23:23   
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/6525fe229e06 [^]
Maturity status: Test
(0072968)
hudsonbot   
2014-12-30 23:23   
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/6525fe229e06 [^]
Maturity status: Test
(0072979)
hudsonbot   
2014-12-30 23:23   
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/6525fe229e06 [^]
Maturity status: Test