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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0027026
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 04. Warehouse managementmajorhave not tried2014-07-07 12:552015-02-24 17:04
ReporteralostaleView Statuspublic 
Assigned Toeduardo_Argal 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revisionbae6b944d95a
ProjectionnoneETAnoneTarget Version3.0PR15Q2
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned ToSandrahuguet
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0027026: 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
TagsNo tags attached.
Attached Filesdiff file icon reserve-idx.diff [^] (1,665 bytes) 2014-07-11 12:49 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 0025796 newdmiguelez Stock Reservation closing feature 
related to defect 0027101 closededuardo_Argal sequential scans on reservation tables on m_get_stock function execution 
blocks defect 0027160 closededuardo_Argal C_Order_Post poor performance 

-  Notes
(0068673)
gorkaion (developer)
2014-07-16 17:55

 The patch with the indexes have been applied as fix of related issue 0027101
(0069055)
hgbot (developer)
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 (developer)
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 (developer)
2014-08-19 15:29

 The issue is not closed for 15 days!!!
Please take an action.
(0069813)
dmitry_mezentsev (developer)
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 (developer)
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 (developer)
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 (developer)
2015-02-24 17:04

 Fixed in issue 27160, C_Order_Post poor performance

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

- Issue History
Date Modified Username Field Change
2014-07-07 12:55 alostale New Issue
2014-07-07 12:55 alostale Assigned To => dmiguelez
2014-07-07 12:55 alostale Modules => Core
2014-07-07 12:55 alostale Triggers an Emergency Pack => No
2014-07-07 12:56 alostale Assigned To dmiguelez => eduardo_Argal
2014-07-07 12:56 alostale Proposed Solution updated
2014-07-07 13:34 jpcalvente Issue Monitored: jpcalvente
2014-07-11 12:49 gorkaion File Added: reserve-idx.diff
2014-07-11 12:50 gorkaion Relationship added related to 0025796
2014-07-16 17:52 gorkaion Issue cloned 0027101
2014-07-16 17:52 gorkaion Relationship added related to 0027101
2014-07-16 17:55 gorkaion Note Added: 0068673
2014-07-21 16:34 jonalegriaesarte Resolution time => 1407448800
2014-07-23 08:26 alostale Relationship added blocks 0027160
2014-07-23 10:58 mtaal Issue Monitored: mtaal
2014-07-28 18:19 eduardo_Argal Assigned To eduardo_Argal => gorkaion
2014-08-04 08:42 hgbot Checkin
2014-08-04 08:42 hgbot Note Added: 0069055
2014-08-04 08:42 hgbot Status new => resolved
2014-08-04 08:42 hgbot Resolution open => fixed
2014-08-04 08:42 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/bae6b944d95adaa6a23f9bd1d30b5a15e3219b63 [^]
2014-08-08 18:54 hudsonbot Checkin
2014-08-08 18:54 hudsonbot Note Added: 0069255
2014-08-19 15:29 dmitry_mezentsev Note Added: 0069516
2014-08-27 18:41 dmitry_mezentsev Note Added: 0069813
2014-08-27 18:41 dmitry_mezentsev Status resolved => new
2014-08-27 18:41 dmitry_mezentsev Resolution fixed => open
2014-08-28 12:12 egoitz Note Added: 0069832
2014-08-28 12:12 egoitz Issue Monitored: egoitz
2014-09-22 16:36 jonalegriaesarte Target Version => 3.0PR15Q1
2014-09-22 16:37 jonalegriaesarte Target Version 3.0PR15Q1 => 3.0PR14Q4
2014-09-29 16:12 jonalegriaesarte Target Version 3.0PR14Q4 => 3.0PR15Q1
2014-10-17 01:07 eintelau Issue Monitored: eintelau
2014-10-17 09:53 dmitry_mezentsev Assigned To gorkaion => eduardo_Argal
2014-12-22 12:37 vmromanos Target Version 3.0PR15Q1 => 3.0PR15Q2
2015-02-24 17:03 Sandrahuguet Note Added: 0074859
2015-02-24 17:04 Sandrahuguet Review Assigned To => Sandrahuguet
2015-02-24 17:04 Sandrahuguet Note Added: 0074860
2015-02-24 17:04 Sandrahuguet Status new => closed
2015-02-24 17:04 Sandrahuguet Resolution open => fixed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker