Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027026Openbravo ERP04. Warehouse managementpublic2014-07-07 12:552015-02-24 17:04
Reporteralostale 
Assigned Toeduardo_Argal 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q2Fixed in Version 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027026: Poor performance of m_get_stock
DescriptionM_Get_Stock function is performing poorly where there are stock reservations.

It does a full scan on m_reservation_stock.
Steps To ReproduceIn an environment with 7K records in m_reservation and 5K in m_reservation_stock.
-Create a reservation
-process it
-check there is a sequential scan caused by m_get_stock
Proposed SolutionProblematic part (consuming 80% of the m_get_stock time) is in -- Insert Values from Storage Detail cursor, more concretely this is the part that causes the sequential scan on m_reservation_stock:

LEFT JOIN (
              SELECT r.m_product_id, r.c_uom_id, rs.m_locator_id, COALESCE(rs.m_attributesetinstance_id, '0') as m_attributesetinstance_id,
                  SUM(rs.quantity - COALESCE(rs.releasedqty, 0)) AS reservedqty
              FROM m_reservation r
                  JOIN m_reservation_stock rs ON r.m_reservation_id = rs.m_reservation_id
              WHERE r.m_product_id = v_productid
                AND r.c_uom_id = COALESCE(v_uomid, r.c_uom_id)
                AND rs.m_locator_id IS NOT NULL
                AND rs.m_locator_id = COALESCE(v_locatorid, rs.m_locator_id)
                AND COALESCE(rs.m_attributesetinstance_id, '0') = COALESCE(v_attributesetinstanceid, rs.m_attributesetinstance_id, '0')
                AND r.res_status NOT IN ('CL', 'DR')
                AND ((v_reservation_id IS NOT NULL AND rs.isallocated = 'Y')
                    OR v_reservation_id IS NULL)
              GROUP BY r.m_product_id, r.c_uom_id, rs.m_locator_id, COALESCE(rs.m_attributesetinstance_id, '0')
            ) res ON t.m_product_id = res.m_product_id
                      AND t.c_uom_id = res.c_uom_id
                      AND COALESCE(t.m_attributesetinstance_id, '0') = res.m_attributesetinstance_id
                      AND t.m_locator_id = res.m_locator_id
Additional Information
TagsNo tags attached.
Relationships
related to feature request 0025796 new dmiguelez Stock Reservation closing feature 
related to defect 0027101 closed eduardo_Argal sequential scans on reservation tables on m_get_stock function execution 
blocks defect 0027160 closed eduardo_Argal C_Order_Post poor performance 
Attached Filesdiff reserve-idx.diff (1,665) 2014-07-11 12:49
https://issues.openbravo.com/file_download.php?file_id=7049&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-07-07 12:55alostaleNew Issue
2014-07-07 12:55alostaleAssigned To => dmiguelez
2014-07-07 12:55alostaleModules => Core
2014-07-07 12:55alostaleTriggers an Emergency Pack => No
2014-07-07 12:56alostaleAssigned Todmiguelez => eduardo_Argal
2014-07-07 12:56alostaleProposed Solution updated
2014-07-07 13:34jpcalventeIssue Monitored: jpcalvente
2014-07-11 12:49gorkaionFile Added: reserve-idx.diff
2014-07-11 12:50gorkaionRelationship addedrelated to 0025796
2014-07-16 17:52gorkaionIssue cloned0027101
2014-07-16 17:52gorkaionRelationship addedrelated to 0027101
2014-07-16 17:55gorkaionNote Added: 0068673
2014-07-21 16:34jonalegriaesarteResolution time => 1407448800
2014-07-23 08:26alostaleRelationship addedblocks 0027160
2014-07-23 10:58mtaalIssue Monitored: mtaal
2014-07-28 18:19eduardo_ArgalAssigned Toeduardo_Argal => gorkaion
2014-08-04 08:42hgbotCheckin
2014-08-04 08:42hgbotNote Added: 0069055
2014-08-04 08:42hgbotStatusnew => resolved
2014-08-04 08:42hgbotResolutionopen => fixed
2014-08-04 08:42hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/bae6b944d95adaa6a23f9bd1d30b5a15e3219b63 [^]
2014-08-08 18:54hudsonbotCheckin
2014-08-08 18:54hudsonbotNote Added: 0069255
2014-08-19 15:29dmitry_mezentsevNote Added: 0069516
2014-08-27 18:41dmitry_mezentsevNote Added: 0069813
2014-08-27 18:41dmitry_mezentsevStatusresolved => new
2014-08-27 18:41dmitry_mezentsevResolutionfixed => open
2014-08-28 12:12egoitzNote Added: 0069832
2014-08-28 12:12egoitzIssue Monitored: egoitz
2014-09-22 16:36jonalegriaesarteTarget Version => 3.0PR15Q1
2014-09-22 16:37jonalegriaesarteTarget Version3.0PR15Q1 => 3.0PR14Q4
2014-09-29 16:12jonalegriaesarteTarget Version3.0PR14Q4 => 3.0PR15Q1
2014-10-17 01:07eintelauIssue Monitored: eintelau
2014-10-17 09:53dmitry_mezentsevAssigned Togorkaion => eduardo_Argal
2014-12-22 12:37vmromanosTarget Version3.0PR15Q1 => 3.0PR15Q2
2015-02-24 17:03SandrahuguetNote Added: 0074859
2015-02-24 17:04SandrahuguetReview Assigned To => Sandrahuguet
2015-02-24 17:04SandrahuguetNote Added: 0074860
2015-02-24 17:04SandrahuguetStatusnew => closed
2015-02-24 17:04SandrahuguetResolutionopen => fixed

Notes
(0068673)
gorkaion   
2014-07-16 17:55   
The patch with the indexes have been applied as fix of related issue 0027101
(0069055)
hgbot   
2014-08-04 08:42   
Repository: erp/devel/pi
Changeset: bae6b944d95adaa6a23f9bd1d30b5a15e3219b63
Author: Gorka Ion Damián <gorkaion.damian <at> openbravo.com>
Date: Mon Aug 04 08:40:38 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/bae6b944d95adaa6a23f9bd1d30b5a15e3219b63 [^]

Fixed issue 27026.Added missing indexes and sql improvement.

---
M src-db/database/model/functions/M_RESERVATION_POST.xml
M src-db/database/model/tables/AD_ORG_WAREHOUSE.xml
M src-db/database/model/tables/M_RESERVATION.xml
M src-db/database/model/tables/M_STOCK_PROPOSED.xml
---
(0069255)
hudsonbot   
2014-08-08 18:54   
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/4450016dee64 [^]
Maturity status: Test
(0069516)
dmitry_mezentsev   
2014-08-19 15:29   
The issue is not closed for 15 days!!!
Please take an action.
(0069813)
dmitry_mezentsev   
2014-08-27 18:41   
Based on the feedback from Asier:

The metrics I took might not be completely relevant, because now most of the time is in m_get_stock which every time is run takes more time due the tables are increasing
* We've removed most of the other problems and now the biggest problem (0000207:0000070% of the time) is in m_get_stock. I think this is the most problematic piece and unless we are able to fix it (might it require a complete refactor??) we won't be able to make this process work
* We've removed most of the (significant) seq scans but still we have 2 seq scans per line to m_reservation_stock table
* M_Reservation_Trg is invoked 3 times per line, can those invacations be grouped into a single 1?
* M_Reservation_Trg takes quite a lot of time, all the updates it does are necessary or some time they can be updating to the same values?

https://docs.google.com/a/openbravo.com/spreadsheets/d/10vAhiNngnOvPChLvdLq-4nDSWnL6IMaQ5MU2IWEi5fM/edit#gid=0 [^]
(0069832)
egoitz   
2014-08-28 12:12   
As a part of the refactor the columns m_locator_id, and m_warehouse_id could be added on the m_stock_proposed table.
This will reduce the complexity when developing warehouse rules.
Right now to filter by locator and/or warehouse it is needed to join with the m_storage_detail table.
A part of creating the columns this should be populated on the m_get_stock function.
(0074859)
Sandrahuguet   
2015-02-24 17:03   
Book an Order

Previously

Book an order of 800 lines -> more than 20 minutes

After the changes

Book an order of 1000 lines -> 4 minutes
Book an order of 10000 lines -> 1 hour
(0074860)
Sandrahuguet   
2015-02-24 17:04   
Fixed in issue 27160, C_Order_Post poor performance

https://code.openbravo.com/erp/devel/pi/rev/7091c40b9be09114b26b6cefeb45bc30c2650bf3 [^]