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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0038886
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 09. Financial managementmajoralways2018-07-03 18:402018-08-20 14:42
ReportermaiteView Statuspublic 
Assigned Tomarkmm82 
PriorityurgentResolutionfixedFixed in Version3.0PR18Q4
StatusclosedFix in branchFixed in SCM revisionb7cdfb272fb3
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned ToSandrahuguet
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

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

- Relationships Relation Graph ] Dependency Graph ]
depends on defect 0039115 closedSandrahuguet API Change: remove Fact_Acct_End_Year_v view 
related to defect 0049339 closedigor_trebol EndYear Process is slow on environment with lots of transactiions 

-  Notes
(0106244)
markmm82 (developer)
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 (developer)
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 (developer)
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 (developer)
2018-08-17 08:38

Code review + testing OK
(0106413)
hudsonbot (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2018-07-03 18:40 maite New Issue
2018-07-03 18:40 maite Assigned To => Triage Finance
2018-07-03 18:40 maite Modules => Core
2018-07-03 18:40 maite Resolution time => 1532383200
2018-07-03 18:40 maite Triggers an Emergency Pack => No
2018-07-03 18:42 maite Issue Monitored: networkb
2018-07-04 08:13 Practics Issue Monitored: Practics
2018-07-16 17:04 markmm82 Assigned To Triage Finance => markmm82
2018-07-16 17:04 markmm82 Status new => acknowledged
2018-07-20 01:25 markmm82 Status acknowledged => scheduled
2018-07-24 12:26 ngarcia Issue Monitored: ngarcia
2018-08-08 15:26 markmm82 Note Added: 0106244
2018-08-08 15:28 markmm82 Note Edited: 0106244 View Revisions
2018-08-09 14:46 Sandrahuguet Relationship added depends on 0039115
2018-08-17 08:27 hgbot Checkin
2018-08-17 08:27 hgbot Note Added: 0106374
2018-08-17 08:27 hgbot Status scheduled => resolved
2018-08-17 08:27 hgbot Resolution open => fixed
2018-08-17 08:27 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/b7cdfb272fb35b9bf83c56040d67bce12fc8135b [^]
2018-08-17 08:27 hgbot Checkin
2018-08-17 08:27 hgbot Note Added: 0106375
2018-08-17 08:38 Sandrahuguet Review Assigned To => Sandrahuguet
2018-08-17 08:38 Sandrahuguet Note Added: 0106377
2018-08-17 08:38 Sandrahuguet Status resolved => closed
2018-08-17 08:38 Sandrahuguet Fixed in Version => 3.0PR18Q4
2018-08-20 14:42 hudsonbot Checkin
2018-08-20 14:42 hudsonbot Note Added: 0106413
2018-08-20 14:42 hudsonbot Checkin
2018-08-20 14:42 hudsonbot Note Added: 0106414
2022-05-30 19:22 aferraz Relationship added related to 0049339


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker