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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0036684
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 02. Master data managementmajorsometimes2017-08-22 10:202017-09-21 16:49
ReporterJONHMView Statuspublic 
Assigned Tomarkmm82 
PriorityhighResolutionfixedFixed in Version3.0PR17Q4
StatusclosedFix in branchFixed in SCM revision6e7197baba41
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0036684: 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
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0098632)
aferraz (manager)
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 (developer)
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 (manager)
2017-08-25 08:57

 Code review + Testing OK
(0098662)
aferraz (manager)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2017-08-22 10:20 JONHM New Issue
2017-08-22 10:20 JONHM Assigned To => Triage Finance
2017-08-22 10:20 JONHM Modules => Core
2017-08-22 10:20 JONHM Resolution time => 1505167200
2017-08-22 10:20 JONHM Triggers an Emergency Pack => No
2017-08-22 10:29 Practics Issue Monitored: Practics
2017-08-22 11:24 aferraz Assigned To Triage Finance => markmm82
2017-08-24 12:53 aferraz Note Added: 0098632
2017-08-25 08:56 hgbot Checkin
2017-08-25 08:56 hgbot Note Added: 0098641
2017-08-25 08:56 hgbot Status new => resolved
2017-08-25 08:56 hgbot Resolution open => fixed
2017-08-25 08:56 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/6e7197baba411d11aa88d9447098cb4394d677a6 [^]
2017-08-25 08:57 aferraz Review Assigned To => aferraz
2017-08-25 08:57 aferraz Note Added: 0098642
2017-08-25 08:57 aferraz Status resolved => closed
2017-08-25 08:57 aferraz Fixed in Version => 3.0PR17Q4
2017-08-25 15:23 aferraz Note Added: 0098662
2017-08-25 15:25 aferraz Note Edited: 0098662 View Revisions
2017-08-29 09:25 aferraz Note Edited: 0098662 View Revisions
2017-09-21 16:49 hudsonbot Checkin
2017-09-21 16:49 hudsonbot Note Added: 0099328

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker