Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027712Openbravo ERP04. Warehouse managementpublic2014-09-30 09:382014-11-04 06:42
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
Summary0027712: slow goods receipt batch insertion
DescriptionBatch insert of good receipts with many lines is slow.
Steps To ReproduceIn a DB with the following data:

m_inout: 500 records
m_inoutline: 220K records
m_attributesetinstance: 225K records
m_storage_detail: 204K records, all of them for the same product ('TEST_IMEI_01')

This testing DB can be downloaded from: https://drive.google.com/a/openbravo.com/file/d/0B2PGxKaZaJn-amwya25HZXE2T3M/view?usp=sharing [^]

Execute the following script to create shipments (attached file to create the script):

select generateGoodReceipt('TEST_IMEI_01', 'TEST_IMEI_01', '10_IMEITEST', 10)

First parameter is a Search Key of a product already existing in F&B Client
Second Parameter is a Search Key of an existing Storage Bin
third parameter is the Document Number you wish for the Goods Receipt

See times before and after applying proposed solution:

10 lines 2600ms -> 120ms
100 lines 23470ms -> 974ms
1000 lines (not tested) -> 5959ms
10000 lines (not tested) -> 60645ms
Proposed SolutionIn this case, most of the time is taken in m_update_inventory function in queries to m_storage_detail, though the are using the unique index the number of records retrieved is high causing slowness.

Proposals:
* Testing it, removing coalesces for m_attributesetinstance_id when possible makes a huge improvement (attached diff). Review it is correct
* Additionally:
  - initial query is doing a count to know if there's ANY value -> don't count, just check if it exists
  - is it possible for initial query to return count > 1? If a single value is expected, id should be stored to be in the other queries
Additional Information
TagsPerformance
Relationships
related to defect 0027713 closed eduardo_Argal slow post of goods shipment if there are many storage detail for that product 
related to defect 0027980 closed dmitry_mezentsev API Break int-857 
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 Files? GenerateGoodsReceipt.sql (6,662) 2014-09-30 09:40
https://issues.openbravo.com/file_download.php?file_id=7280&type=bug
diff update-inventory.diff (4,233) 2014-09-30 09:47
https://issues.openbravo.com/file_download.php?file_id=7281&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-09-30 09:38alostaleNew Issue
2014-09-30 09:38alostaleAssigned To => dmiguelez
2014-09-30 09:38alostaleModules => Core
2014-09-30 09:38alostaleTriggers an Emergency Pack => No
2014-09-30 09:39alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=6685#r6685
2014-09-30 09:40alostaleFile Added: GenerateGoodsReceipt.sql
2014-09-30 09:40alostaleSummaryslow shipment batch insertion => slow goods receipt batch insertion
2014-09-30 09:40alostaleDescription Updatedbug_revision_view_page.php?rev_id=6687#r6687
2014-09-30 09:45alostaleTag Attached: Performance
2014-09-30 09:47alostaleFile Added: update-inventory.diff
2014-09-30 09:50alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=6688#r6688
2014-09-30 09:57alostaleRelationship addedrelated to 0027713
2014-09-30 09:58alostaleIssue Monitored: alostale
2014-09-30 11:25jonalegriaesarteResolution time => 1414623600
2014-09-30 11:25jonalegriaesarteSeverityminor => major
2014-10-09 16:45jpcalventeIssue Monitored: jpcalvente
2014-10-21 18:49eduardo_ArgalStatusnew => scheduled
2014-10-21 18:49eduardo_Argalfix_in_branch => pi
2014-10-21 18:49eduardo_ArgalStatusscheduled => resolved
2014-10-21 18:49eduardo_ArgalFixed in SCM revision => https://code.openbravo.com/erp/devel/pi/rev/c1d30f85a429d63b04ee6470072ec5b1742acc5e [^]
2014-10-21 18:49eduardo_ArgalResolutionopen => fixed
2014-10-21 18:50eduardo_ArgalNote Added: 0071103
2014-10-21 18:50eduardo_ArgalNote Added: 0071104
2014-10-24 10:49hgbotCheckin
2014-10-24 10:49hgbotNote Added: 0071160
2014-10-24 18:41eduardo_ArgalRelationship addedrelated to 0027980
2014-10-31 13:09dmitry_mezentsevAssigned Todmiguelez => Sandrahuguet
2014-10-31 13:22SandrahuguetAssigned ToSandrahuguet => eduardo_Argal
2014-11-04 06:42pramakrishnanReview Assigned To => pramakrishnan
2014-11-04 06:42pramakrishnanNote Added: 0071386
2014-11-04 06:42pramakrishnanStatusresolved => closed
2015-03-10 09:59ngarciaRelationship addedcauses 0029211

Notes
(0071103)
eduardo_Argal   
2014-10-21 18:50   
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
(0071104)
eduardo_Argal   
2014-10-21 18:50   
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
---
(0071160)
hgbot   
2014-10-24 10:49   
Repository: erp/devel/api-checks
Changeset: be3bd7ba7f26568a14e1491003d742029d7f30b2
Author: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
Date: Fri Oct 24 10:48:28 2014 +0200
URL: http://code.openbravo.com/erp/devel/api-checks/rev/be3bd7ba7f26568a14e1491003d742029d7f30b2 [^]

Related with issue 27712: Fixes API Change for NOT NULL column M_ATTRIBUTESETINSTANCE_ID

There is no risk in setting it to NOT NULL as a default an onCreateDefault have been added. This column was already being populated in most of the cases with '0' as default value. It was forcing many sql to use coalesce while just few records were null (although many already contained '0')

---
M model/src-db/database/model/tables/M_RESERVATION_STOCK.xml
M model/src-db/database/model/tables/M_STORAGE_DETAIL.xml
---
(0071386)
pramakrishnan   
2014-11-04 06:42   
Code Review + Testing - OK.