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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0027713
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajorhave not tried2014-09-30 09:552014-12-30 23:23
ReporteralostaleView Statuspublic 
Assigned Toeduardo_Argal 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision5223d4881147
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Topramakrishnan
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0027713: 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
TagsPerformance
Attached Filesdiff file icon issue-27713.diff [^] (1,909 bytes) 2014-09-30 10:01 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0027712 closededuardo_Argal slow goods receipt batch insertion 
related to defect 0027733 newdmiguelez M_InOut post allows modifications while the document is being processed 
related to defect 0027740 closededuardo_Argal poor performance when completing a shipment with many lines 
causes defect 0029211 closededuardo_Argal Cannot complete a goods receipt with a line without attribute set value if it has a related prereservation 

-  Notes
(0071101)
hgbot (developer)
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 (viewer)
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 (developer)
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 (developer)
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 (viewer)
2014-11-04 06:44

 Code Review + Testing - OK.
(0072964)
hudsonbot (viewer)
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 (viewer)
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 (viewer)
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

- Issue History
Date Modified Username Field Change
2014-09-30 09:55 alostale New Issue
2014-09-30 09:55 alostale Assigned To => dmiguelez
2014-09-30 09:55 alostale OBNetwork customer => No
2014-09-30 09:55 alostale Modules => Core
2014-09-30 09:55 alostale Triggers an Emergency Pack => No
2014-09-30 09:56 alostale Tag Attached: Performance
2014-09-30 09:56 alostale Proposed Solution updated
2014-09-30 09:57 alostale Relationship added related to 0027712
2014-09-30 09:58 alostale Issue Monitored: alostale
2014-09-30 10:00 alostale Proposed Solution updated
2014-09-30 10:01 alostale File Added: issue-27713.diff
2014-09-30 11:26 jonalegriaesarte OBNetwork customer No => Yes
2014-09-30 11:26 jonalegriaesarte Resolution time => 1414623600
2014-09-30 11:26 jonalegriaesarte Severity minor => major
2014-10-02 07:58 alostale Relationship added related to 0027733
2014-10-02 12:45 egoitz Relationship added related to 0027740
2014-10-09 16:45 jpcalvente Issue Monitored: jpcalvente
2014-10-21 18:40 hgbot Checkin
2014-10-21 18:40 hgbot Note Added: 0071101
2014-10-21 18:40 hgbot Status new => resolved
2014-10-21 18:40 hgbot Resolution open => fixed
2014-10-21 18:40 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^]
2014-10-22 10:19 umartirena Note Added: 0071112
2014-10-22 10:19 umartirena Status resolved => new
2014-10-22 10:19 umartirena Resolution fixed => open
2014-10-22 10:20 umartirena Note Edited: 0071112 View Revisions
2014-10-22 10:24 hgbot Checkin
2014-10-22 10:24 hgbot Note Added: 0071113
2014-10-22 10:24 hgbot Status new => resolved
2014-10-22 10:24 hgbot Resolution open => fixed
2014-10-22 10:24 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^] => http://code.openbravo.com/erp/devel/pi/rev/5223d48811474fe528f71aa2fd33d090b19bb899 [^]
2014-10-23 18:05 hgbot Checkin
2014-10-23 18:05 hgbot Note Added: 0071146
2014-10-31 13:09 dmitry_mezentsev Assigned To dmiguelez => Sandrahuguet
2014-10-31 13:22 Sandrahuguet Assigned To Sandrahuguet => eduardo_Argal
2014-11-04 06:44 pramakrishnan Review Assigned To => pramakrishnan
2014-11-04 06:44 pramakrishnan Note Added: 0071387
2014-11-04 06:44 pramakrishnan Status resolved => closed
2014-12-30 23:23 hudsonbot Checkin
2014-12-30 23:23 hudsonbot Note Added: 0072964
2014-12-30 23:23 hudsonbot Checkin
2014-12-30 23:23 hudsonbot Note Added: 0072968
2014-12-30 23:23 hudsonbot Checkin
2014-12-30 23:23 hudsonbot Note Added: 0072979
2015-03-10 09:59 ngarcia Relationship added causes 0029211

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker