0038886: Performance problem in End Year Close | Accounting tab

Notes
(0106244) markmm82 (viewer) 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 (viewer) 2018-08-17 08:38	Code review + testing OK

(0106413) hudsonbot (viewer) 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 (viewer) 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	OBNetwork customer	=> Yes
2018-07-03 18:40	maite	Modules	=> Core
2018-07-03 18:40	maite	Support ticket	=> 2997
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