Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0039293Openbravo ERP02. Master data managementpublic2018-09-14 13:162018-09-25 20:57
Reporterjoniturralde93 
Assigned Tomarkmm82 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version3.0PR18Q2 
Target VersionFixed in Version3.0PR18Q4 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket4651
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0039293: Accounting Transaction Details Query Performance
DescriptionWe reproduced this is Metal-US test environment:

Just load Accounting Transaction Details Window, the query is too slow and timeout is thrown. Attached query (with no filters) and Query plan (explain analyze).

Same query in the database took 661 seconds.
Steps To Reproduce1. Log into an environment with a lot of data.
2. Open Accounting Transaction Details Window.
3. It tooks for too long and you get a timeout.
Proposed Solution
Additional Information
TagsPerformance
Relationships
related to defect 0039201 closed jarmendariz Menu may be overlapped by a messageBar if receiving a response from a closed window request 
related to feature request 0039380 closed AugustoMauch Support using both "-columnName" and "columnName desc" in HQL order by clauses 
Attached Files? QueryMetal (24,335) 2018-09-14 13:16
https://issues.openbravo.com/file_download.php?file_id=12140&type=bug
Issue History
Date ModifiedUsernameFieldChange
2018-09-14 13:16joniturralde93New Issue
2018-09-14 13:16joniturralde93Assigned To => Triage Finance
2018-09-14 13:16joniturralde93File Added: QueryMetal
2018-09-14 13:16joniturralde93OBNetwork customer => Yes
2018-09-14 13:16joniturralde93Modules => Core
2018-09-14 13:16joniturralde93Support ticket => 4651
2018-09-14 13:16joniturralde93Resolution time => 1538690400
2018-09-14 13:16joniturralde93Triggers an Emergency Pack => No
2018-09-14 13:18joniturralde93SummaryAccounting Transaction Query Performance => Accounting Transaction Details Query Performance
2018-09-14 13:18joniturralde93Description Updatedbug_revision_view_page.php?rev_id=17638#r17638
2018-09-14 13:18joniturralde93Steps to Reproduce Updatedbug_revision_view_page.php?rev_id=17640#r17640
2018-09-17 09:19SandrahuguetAssigned ToTriage Finance => markmm82
2018-09-17 13:38SandrahuguetTag Attached: Performance
2018-09-17 16:04rafarodaIssue Monitored: rafaroda
2018-09-17 16:12rafarodaRelationship addedrelated to 0039201
2018-09-19 16:32markmm82Statusnew => scheduled
2018-09-21 17:26markmm82Note Added: 0106944
2018-09-25 16:50hgbotCheckin
2018-09-25 16:50hgbotNote Added: 0107023
2018-09-25 16:50hgbotStatusscheduled => resolved
2018-09-25 16:50hgbotResolutionopen => fixed
2018-09-25 16:50hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/acd8c31a80d01a4e5327f40e6777b28df7c8c345 [^]
2018-09-25 16:50hgbotCheckin
2018-09-25 16:50hgbotNote Added: 0107024
2018-09-25 17:07SandrahuguetReview Assigned To => Sandrahuguet
2018-09-25 17:07SandrahuguetNote Added: 0107025
2018-09-25 17:07SandrahuguetStatusresolved => closed
2018-09-25 17:07SandrahuguetFixed in Version => 3.0PR18Q4
2018-09-25 20:57hudsonbotCheckin
2018-09-25 20:57hudsonbotNote Added: 0107033
2018-09-25 20:57hudsonbotCheckin
2018-09-25 20:57hudsonbotNote Added: 0107034
2018-09-28 15:03SandrahuguetRelationship addedrelated to 0039380

Notes
(0106944)
markmm82   
2018-09-21 17:26   
Before solution:
Cost: 10604152.66 Execution time: 661680.116 ms

After:
Cost: 185.12 Execution time: 18.116 ms

There is a great improvement in the new solution. The cost and the execution time are highly improved.
(0107023)
hgbot   
2018-09-25 16:50   
Repository: erp/devel/pi
Changeset: acd8c31a80d01a4e5327f40e6777b28df7c8c345
Author: Mark <markmm82 <at> gmail.com>
Date: Fri Sep 21 11:09:12 2018 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/acd8c31a80d01a4e5327f40e6777b28df7c8c345 [^]

Fixes issue 39293: Improved Performance in Accounting Transaction Details Query

Updated the FACT_ACCT_DATEACCT index to be a multi-column index on DATEACCT and
FACT_ACCT_ID columns, and changed the order by clause of the tab to use it.

---
M src-db/database/model/tables/FACT_ACCT.xml
M src-db/database/sourcedata/AD_TAB.xml
---
(0107024)
hgbot   
2018-09-25 16:50   
Repository: erp/devel/pi
Changeset: 646304f60e8f2c2e9090e5b8cba2e0660d6e863f
Author: Sandra Huguet <sandra.huguet <at> openbravo.com>
Date: Tue Sep 25 16:47:57 2018 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/646304f60e8f2c2e9090e5b8cba2e0660d6e863f [^]

related to issue 39293 code review changes
modify the hql where clause to correctly apply the
id of the table + DESC order

---
M src-db/database/sourcedata/AD_TAB.xml
---
(0107025)
Sandrahuguet   
2018-09-25 17:07   
code review + testing OK
(0107033)
hudsonbot   
2018-09-25 20:57   
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/b69e30e117b8 [^]
Maturity status: Test
(0107034)
hudsonbot   
2018-09-25 20:57   
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/b69e30e117b8 [^]
Maturity status: Test