Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038886Openbravo ERP09. Financial managementpublic2018-07-03 18:402018-08-20 14:42
Reportermaite 
Assigned Tomarkmm82 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q4 
Merge Request Status
Review Assigned ToSandrahuguet
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket2997
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0038886: Performance problem in End Year Close | Accounting tab
DescriptionIn an environment with about 4.500.000 records in fact_acct table takes around 2 minutes to show Accounting data (where finally only 39 records are shown, for example)
Steps To ReproducePerformance of Fact_Acct_End_Year_v should be improved
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on defect 0039115 closed Sandrahuguet API Change: remove Fact_Acct_End_Year_v view 
related to defect 0049339 closed igor_trebol EndYear Process is slow on environment with lots of transactiions 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-07-03 18:40maiteNew Issue
2018-07-03 18:40maiteAssigned To => Triage Finance
2018-07-03 18:40maiteOBNetwork customer => Yes
2018-07-03 18:40maiteModules => Core
2018-07-03 18:40maiteSupport ticket => 2997
2018-07-03 18:40maiteResolution time => 1532383200
2018-07-03 18:40maiteTriggers an Emergency Pack => No
2018-07-03 18:42maiteIssue Monitored: networkb
2018-07-04 08:13PracticsIssue Monitored: Practics
2018-07-16 17:04markmm82Assigned ToTriage Finance => markmm82
2018-07-16 17:04markmm82Statusnew => acknowledged
2018-07-20 01:25markmm82Statusacknowledged => scheduled
2018-07-24 12:26ngarciaIssue Monitored: ngarcia
2018-08-08 15:26markmm82Note Added: 0106244
2018-08-08 15:28markmm82Note Edited: 0106244bug_revision_view_page.php?bugnote_id=0106244#r17514
2018-08-09 14:46SandrahuguetRelationship addeddepends on 0039115
2018-08-17 08:27hgbotCheckin
2018-08-17 08:27hgbotNote Added: 0106374
2018-08-17 08:27hgbotStatusscheduled => resolved
2018-08-17 08:27hgbotResolutionopen => fixed
2018-08-17 08:27hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/b7cdfb272fb35b9bf83c56040d67bce12fc8135b [^]
2018-08-17 08:27hgbotCheckin
2018-08-17 08:27hgbotNote Added: 0106375
2018-08-17 08:38SandrahuguetReview Assigned To => Sandrahuguet
2018-08-17 08:38SandrahuguetNote Added: 0106377
2018-08-17 08:38SandrahuguetStatusresolved => closed
2018-08-17 08:38SandrahuguetFixed in Version => 3.0PR18Q4
2018-08-20 14:42hudsonbotCheckin
2018-08-20 14:42hudsonbotNote Added: 0106413
2018-08-20 14:42hudsonbotCheckin
2018-08-20 14:42hudsonbotNote Added: 0106414
2022-05-30 19:22aferrazRelationship addedrelated to 0049339

Notes
(0106244)
markmm82   
2018-08-08 15:26   
(edited on: 2018-08-08 15:28)
In an environment with about 4.500.000 records in fact_acct table:
 
Performance of query before fix:
Cost: 685259.85
Time: 112373.456

Performance of query after fix:
Cost: 492672.85
Time: 8986.946

Cost and time are improved with the fix of the issue.
(0106374)
hgbot   
2018-08-17 08:27   
Repository: erp/devel/pi
Changeset: b7cdfb272fb35b9bf83c56040d67bce12fc8135b
Author: Mark <markmm82 <at> gmail.com>
Date: Mon Aug 13 10:59:49 2018 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/b7cdfb272fb35b9bf83c56040d67bce12fc8135b [^]

Fixes issue 38886: Performance problem in End Year Close | Accounting tab

To improve performance several changes were applied:
- Added index on FACTACCTTYPE column of FACT_ACCT table.
- Created a new HQL Table to improve the table/query used to load the Accounting tab
  instead of the used Fact_Acct_End_Year_v view.
- The new HQL query uses the AD_ORG.AD_Periodcontrolallowed_Org_ID column instead of
  using the ad_org_getperiodcontrolallow(fact_acct.ad_org_id) function.
- Also, it avoids do join with C_ElementValue and C_AcctSchema tables in the FROM
  clause of the query.
- Removed not needed subquery in the FROM clause.
- Added filtering by selected Period Control in header.
- Removed the ordering by C_ElementValue.value and C_AcctSchema.name as removed join
  with tables was affecting the performance.
- All fields of the Accounting tab changed their columns to the ones created in the new HQL
  table.

---
M src-db/database/model/tables/FACT_ACCT.xml
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_FIELD.xml
M src-db/database/sourcedata/AD_TAB.xml
M src-db/database/sourcedata/AD_TABLE.xml
---
(0106375)
hgbot   
2018-08-17 08:27   
Repository: erp/devel/pi
Changeset: 41702504daa5fd445a0a0d96f7e097ac3a40b575
Author: Mark <markmm82 <at> gmail.com>
Date: Tue Aug 07 19:54:15 2018 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/41702504daa5fd445a0a0d96f7e097ac3a40b575 [^]

Related to issue 38886: Removed unused view

Removed columns and tables related to FACT_ACCT_END_YEAR_V view in AD and database.

---
M src-db/database/sourcedata/AD_COLUMN.xml
M src-db/database/sourcedata/AD_TABLE.xml
R src-db/database/model/views/FACT_ACCT_END_YEAR_V.xml
---
(0106377)
Sandrahuguet   
2018-08-17 08:38   
Code review + testing OK
(0106413)
hudsonbot   
2018-08-20 14:42   
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/4f7d5fed140f [^]
Maturity status: Test
(0106414)
hudsonbot   
2018-08-20 14:42   
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/4f7d5fed140f [^]
Maturity status: Test