Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0031977Openbravo ERP03. Procurement managementpublic2016-01-21 11:092016-03-17 10:55
Reportermaite 
Assigned Tovmromanos 
PriorityimmediateSeveritymajorReproducibilityalways
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Version 
Target Version3.0PR16Q2Fixed in Version 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0031977: 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
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Filesdiff Fixes31977.diff (11,364) 2016-01-31 17:14
https://issues.openbravo.com/file_download.php?file_id=8986&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-01-21 11:09maiteNew Issue
2016-01-21 11:09maiteAssigned To => Triage Finance
2016-01-21 11:09maiteModules => Core
2016-01-21 11:09maiteResolution time => 1456182000
2016-01-21 11:09maiteTriggers an Emergency Pack => No
2016-01-21 11:09maiteIssue Monitored: networkb
2016-01-21 11:17maiteTarget Version => 3.0PR16Q2
2016-01-21 18:43egoitzIssue Monitored: egoitz
2016-01-25 09:10egoitzResolution time1456182000 => 1454194800
2016-01-31 12:57vmromanosStatusnew => scheduled
2016-01-31 12:57vmromanosAssigned ToTriage Finance => vmromanos
2016-01-31 16:09vmromanosNote Added: 0083761
2016-01-31 16:55vmromanosNote Edited: 0083761bug_revision_view_page.php?bugnote_id=0083761#r10835
2016-01-31 17:14vmromanosFile Added: Fixes31977.diff
2016-02-02 11:30hgbotCheckin
2016-02-02 11:30hgbotNote Added: 0083838
2016-02-02 11:30hgbotStatusscheduled => resolved
2016-02-02 11:30hgbotResolutionopen => fixed
2016-02-02 11:30hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/34a1c46de55be104e0621698a15e41b3c015c491 [^]
2016-02-02 11:31aferrazReview Assigned To => aferraz
2016-02-02 11:31aferrazNote Added: 0083839
2016-02-02 11:31aferrazStatusresolved => closed
2016-02-02 11:31aferrazFixed in Version => 3.0PR16Q2
2016-02-03 09:31egoitzNote Added: 0083884
2016-02-03 09:31egoitzStatusclosed => new
2016-02-03 09:31egoitzResolutionfixed => open
2016-02-03 09:31egoitzFixed in Version3.0PR16Q2 =>
2016-02-08 09:54vmromanosNote Added: 0083996
2016-02-08 09:54vmromanosStatusnew => closed
2016-02-08 09:54vmromanosResolutionopen => no change required
2016-02-16 18:43egoitzNote Added: 0084269
2016-02-16 18:43egoitzStatusclosed => new
2016-02-16 18:43egoitzResolutionno change required => open
2016-02-19 11:49vmromanosNote Added: 0084356
2016-02-19 11:49vmromanosStatusnew => closed
2016-02-19 11:49vmromanosResolutionopen => no change required
2016-03-17 10:55hudsonbotCheckin
2016-03-17 10:55hudsonbotNote Added: 0085100

Notes
(0083761)
vmromanos   
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   
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   
2016-02-02 11:31   
Code review + Testing OK
(0083884)
egoitz   
2016-02-03 09:31   
The problem has not been solved.
(0083996)
vmromanos   
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   
2016-02-16 18:43   
there are still cases where the performance is not good
(0084356)
vmromanos   
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   
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