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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0031977
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 03. Procurement managementmajoralways2016-01-21 11:092016-03-17 10:55
ReportermaiteView Statuspublic 
Assigned Tovmromanos 
PriorityimmediateResolutionno change requiredFixed in Version
StatusclosedFix in branchFixed in SCM revision34a1c46de55b
ProjectionnoneETAnoneTarget Version3.0PR16Q2
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket40288
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0031977: Performance problem in ReturnMaterialShipmentPickEdit datasource

DescriptionReturnMaterialShipmentPickEdit takes around 40 seconds to obtain result of 3 records
Steps To ReproduceI can provide credentials to specific environment where problem is reproducible
TagsNo tags attached.
Attached Filesdiff file icon Fixes31977.diff [^] (11,364 bytes) 2016-01-31 17:14 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0083761)
vmromanos (viewer)
2016-01-31 16:09
edited on: 2016-01-31 16:55

 Test plan I:

As Openbravo Admin

Create a new Return to Vendor document.
  Business Partner: Bebidas alegres
Press Pick/Edit Lines
  Select any line, set any amount > 0
  Press Done
Book Document

Create a new Return to Vendor Shipment document.
  Business Partner: Bebidas alegres
Press Pick/Edit Lines
Verify the previous record is shown

Create a new Return to Vendor document.
  Business Partner: Bebidas alegres
Press Pick/Edit Lines
  Select several lines, set any amount > 0
  Press Done
Book Document

Create a new Return to Vendor Shipment document.
  Business Partner: Bebidas alegres
Press Pick/Edit Lines
Verify the previous records are shown
Verify they are ordered by RM Order No. and Line No.
Select two of done and press done.
Press Pick/Edit Lines
Verify they are ordered by Selected, RM Order No. and Line No.



Test plan II:

In an environment with performance problems
Create a new Return to Vendor Shipment document for any Business Partner
Press Pick/Edit Lines.
Verify the time to get records have been reduced
(0083838)
hgbot (developer)
2016-02-02 11:30

 Repository: erp/devel/pi
Changeset: 34a1c46de55be104e0621698a15e41b3c015c491
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Sun Jan 31 17:05:32 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/34a1c46de55be104e0621698a15e41b3c015c491 [^]

Fixed bug 31977: Performance problem in ReturnMaterialShipmentPickEdit

The "HQL where" and "HQL order by" clauses in RM Shipment Pick and Edit | Pick Edit Lines tab created a non-optimal SQL query with important performance issues in high volume environments, specially in the ones with many order lines and order headers. In particular, the query included unnecessary:
* Left join to m_product
* Cross join to c_orderline
* Cross join to c_order

The solution:
* Add the c_bpartner_id column to the M_RM_SHIPMENT_PICK_EDIT view and filter by this column in the tab's HQL where clause, so we avoid the need to cross join over c_orderline and c_order tables.
* The "HQL order by" orders by lineNo (instead of product), so we avoid the need to left join over m_product and we show a better default order (selected, RM Order No. and Line No.)

With these changes, the SQL cost calculated by the planner has been reduced a 88% in our internal testing (from 5100 to 609)

---
M src-db/database/model/views/M_RM_SHIPMENT_PICK_EDIT.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_TAB.xml
---
(0083839)
aferraz (viewer)
2016-02-02 11:31

 Code review + Testing OK
(0083884)
egoitz (viewer)
2016-02-03 09:31

 The problem has not been solved.
(0083996)
vmromanos (viewer)
2016-02-08 09:54

 This issue was reopened by mistake.
The fix does reduce the time taken by this process from 1 minute to 1 second in the customer's environment.
(0084269)
egoitz (viewer)
2016-02-16 18:43

 there are still cases where the performance is not good
(0084356)
vmromanos (viewer)
2016-02-19 11:49

 Closed again once confirmed the issue is not reproducible in PI, only in 15Q2 or lower.

Provided a patch for the customer's environment and tested in staging machine.
(0085100)
hudsonbot (viewer)
2016-03-17 10:55

 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/b22fb0500156 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2016-01-21 11:09 maite New Issue
2016-01-21 11:09 maite Assigned To => Triage Finance
2016-01-21 11:09 maite OBNetwork customer => Yes
2016-01-21 11:09 maite Modules => Core
2016-01-21 11:09 maite Support ticket => 40288
2016-01-21 11:09 maite Resolution time => 1456182000
2016-01-21 11:09 maite Triggers an Emergency Pack => No
2016-01-21 11:09 maite Issue Monitored: networkb
2016-01-21 11:17 maite Target Version => 3.0PR16Q2
2016-01-21 18:43 egoitz Issue Monitored: egoitz
2016-01-25 09:10 egoitz Resolution time 1456182000 => 1454194800
2016-01-31 12:57 vmromanos Status new => scheduled
2016-01-31 12:57 vmromanos Assigned To Triage Finance => vmromanos
2016-01-31 16:09 vmromanos Note Added: 0083761
2016-01-31 16:55 vmromanos Note Edited: 0083761 View Revisions
2016-01-31 17:14 vmromanos File Added: Fixes31977.diff
2016-02-02 11:30 hgbot Checkin
2016-02-02 11:30 hgbot Note Added: 0083838
2016-02-02 11:30 hgbot Status scheduled => resolved
2016-02-02 11:30 hgbot Resolution open => fixed
2016-02-02 11:30 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/34a1c46de55be104e0621698a15e41b3c015c491 [^]
2016-02-02 11:31 aferraz Review Assigned To => aferraz
2016-02-02 11:31 aferraz Note Added: 0083839
2016-02-02 11:31 aferraz Status resolved => closed
2016-02-02 11:31 aferraz Fixed in Version => 3.0PR16Q2
2016-02-03 09:31 egoitz Note Added: 0083884
2016-02-03 09:31 egoitz Status closed => new
2016-02-03 09:31 egoitz Resolution fixed => open
2016-02-03 09:31 egoitz Fixed in Version 3.0PR16Q2 =>
2016-02-08 09:54 vmromanos Note Added: 0083996
2016-02-08 09:54 vmromanos Status new => closed
2016-02-08 09:54 vmromanos Resolution open => no change required
2016-02-16 18:43 egoitz Note Added: 0084269
2016-02-16 18:43 egoitz Status closed => new
2016-02-16 18:43 egoitz Resolution no change required => open
2016-02-19 11:49 vmromanos Note Added: 0084356
2016-02-19 11:49 vmromanos Status new => closed
2016-02-19 11:49 vmromanos Resolution open => no change required
2016-03-17 10:55 hudsonbot Checkin
2016-03-17 10:55 hudsonbot Note Added: 0085100

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker