Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0036684Openbravo ERP02. Master data managementpublic2017-08-22 10:202017-09-21 16:49
ReporterJONHM 
Assigned Tomarkmm82 
PriorityhighSeveritymajorReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q4 
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
Summary0036684: Performance issue in Goods Receipt selector of 'Landed Cost' window
DescriptionHaving an environment with lots of receipts, the "M_InOut Receipts" selector has bad performance.
Steps To Reproducem_inoutline has 1832153 lines
m_transaction has 2919502 lines

1) Open 'Landed Cost' window and create a new record
2) Switch to 'Receipt' tab
3) Create a new line and open 'Goods recepit' selector

It took almost 1 minute to show data
Proposed SolutionThe issue seems to be caused by the HQL where clause of the 'Receipts' selector included in 'M_InOut Receipts' reference
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-08-22 10:20JONHMNew Issue
2017-08-22 10:20JONHMAssigned To => Triage Finance
2017-08-22 10:20JONHMModules => Core
2017-08-22 10:20JONHMResolution time => 1505167200
2017-08-22 10:20JONHMTriggers an Emergency Pack => No
2017-08-22 10:29PracticsIssue Monitored: Practics
2017-08-22 11:24aferrazAssigned ToTriage Finance => markmm82
2017-08-24 12:53aferrazNote Added: 0098632
2017-08-25 08:56hgbotCheckin
2017-08-25 08:56hgbotNote Added: 0098641
2017-08-25 08:56hgbotStatusnew => resolved
2017-08-25 08:56hgbotResolutionopen => fixed
2017-08-25 08:56hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/6e7197baba411d11aa88d9447098cb4394d677a6 [^]
2017-08-25 08:57aferrazReview Assigned To => aferraz
2017-08-25 08:57aferrazNote Added: 0098642
2017-08-25 08:57aferrazStatusresolved => closed
2017-08-25 08:57aferrazFixed in Version => 3.0PR17Q4
2017-08-25 15:23aferrazNote Added: 0098662
2017-08-25 15:25aferrazNote Edited: 0098662bug_revision_view_page.php?bugnote_id=0098662#r15722
2017-08-29 09:25aferrazNote Edited: 0098662bug_revision_view_page.php?bugnote_id=0098662#r15733
2017-09-21 16:49hudsonbotCheckin
2017-09-21 16:49hudsonbotNote Added: 0099328

Notes
(0098632)
aferraz   
2017-08-24 12:53   
Current SQL takes more than 1 minute in client environment:

SELECT *
FROM m_inout io
JOIN c_bpartner bp
ON io.c_bpartner_id = bp.c_bpartner_id
WHERE io.issotrx = 'N'
AND io.docstatus = 'CO'
AND EXISTS (
  SELECT 1
  FROM m_inoutline iol
  WHERE iol.m_inout_id = io.m_inout_id
  AND iol.movementqty >= 0
)
AND EXISTS (
  SELECT 1
  FROM m_transaction t
  CROSS JOIN m_costing_rule cr
  CROSS JOIN m_inoutline iol
  WHERE t.m_inoutline_id = iol.m_inoutline_id
  AND iol.m_inout_id = io.m_inout_id
  AND cr.isvalidated = 'Y'
  AND Ad_org_isinnaturaltree(cr.ad_org_id, t.ad_org_id, t.ad_client_id) = 'Y'
  AND cr.dateto IS NULL
  AND (cr.datefrom IS NULL
  OR cr.datefrom <= t.trxprocessdate)
)
ORDER BY io.documentno, io.movementdate, bp.name, io.m_inout_id;

Improved SQL takes around 20 seconds in client environment:

SELECT *
FROM m_inout io
JOIN c_bpartner bp
ON io.c_bpartner_id = bp.c_bpartner_id
WHERE io.issotrx = 'N'
AND io.docstatus = 'CO'
AND EXISTS (
  SELECT 1
  FROM m_inoutline iol
  JOIN m_transaction t
  ON iol.m_inoutline_id = t.m_inoutline_id
  WHERE iol.m_inout_id = io.m_inout_id
  AND iol.movementqty >= 0
  AND EXISTS (
    SELECT 1
    FROM m_costing_rule cr
    WHERE cr.isvalidated = 'Y'
    AND ad_org_isinnaturaltree(cr.ad_org_id, t.ad_org_id, t.ad_client_id) = 'Y'
    AND cr.dateto IS NULL
    AND (cr.datefrom IS NULL
    OR cr.datefrom <= t.trxprocessdate)
  )
)
ORDER BY io.documentno, io.movementdate, bp.name, io.m_inout_id;
(0098641)
hgbot   
2017-08-25 08:56   
Repository: erp/devel/pi
Changeset: 6e7197baba411d11aa88d9447098cb4394d677a6
Author: Mark <markmm82 <at> gmail.com>
Date: Tue Aug 15 15:00:49 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/6e7197baba411d11aa88d9447098cb4394d677a6 [^]

Fixes issue 36684: Performance problem in Goods Receipt selector

Performance problem in Goods Receipt selector of Landed Cost window.

Selector whereclause was refactored using exists and avoid cross joins to improve performance.

---
M src-db/database/sourcedata/OBUISEL_SELECTOR.xml
---
(0098642)
aferraz   
2017-08-25 08:57   
Code review + Testing OK
(0098662)
aferraz   
2017-08-25 15:23   
(edited on: 2017-08-29 09:25)
SQL query could be improved even more (taking around 15 ms.) changing the order by clause. We have two options:
1- Order by io.documentno, io.m_inout_id -> this will use the actual m_inout_documentno_id index.
2- Order by io.documentno, io.movementdate, io.m_inout_id -> add movementdate to m_inout_documentno_id index.
Actual order by includes bp.name, so query doesn't use above indexes.

However, it is not possible to change the order by clause of Selector query.
Order by is automatically created using table identifiers.

The only way to change it is by setting the Display Field in the Selector.
We can set "Document No." as display field and query will only be ordered by io.documentno and io.m_inout_id, using m_inout_documentno_id index.
This change will make the selector show only Receipt documentno, istead of documentno, movementdate and bp.
(0099328)
hudsonbot   
2017-09-21 16:49   
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/9750b78d3e5c [^]
Maturity status: Test