Performance problem issue when we run financial
report. With hundred thousands records in fact_acct
table, you will be amazed how slow it was. You can
even make a coffee break. :-p
In my case this report is based on a table rather than on a
View so every you ask for a financial report. Compiere
creates the temporal table for this report, that's the
reason why it takes so long.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Armen, I found a workaround, before you run the Financial Report, you can run the process "Performance Analysis > Accounting Rules > Update Accounting Balance" with parameter "Recreate Data" checked.
This process is very fast compared to the "Update Balances" on Financial Report (because only delete/insert, instead of update) and even is more secure, because the "Update Balances" on Financial Report doesn't delete from temporary tables the accounting entries deleted.
So, after you recreated the balances, you can run financial reports with parameter "Update Balances" unchecked and they are very fast.
Please tell me if it works, maybe we can change the Financial Report process to use the "Recreate Data" always instead of trying to update every time the table.
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Actually that's what I did all this time. I made the parameter always "True" in the code.
Yes they're faster but not that fast we are expecting for a robust accounting software.
I hope there are another workaround possible. Anyway, thank you for taking care this tracker.
Keep the good work and spirit.
Armen
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I will describe how our legacy system provides instant financials with hundreds of thousands of LEDGER_ACCT_DAILY records (the equivalent of Fact_Acct_Balance) and millions of LEDGER_DETAIL records (the equivalent of Fact_acct). There are several levels of summary.
First, dates are converted to fiscal period = fiscal year * 100 + period. This could be Fact_Acct_Sum. In addition, period is 1 origin, and the summary table uses period 0 for starting balances for the fiscal year. Now, to compute the balance at a given date, you start at period 0 of that fiscal year in Fact_Acct_sum (start at 1 for YTD balance), and sum through the previous whole period. Then you sum through Fact_Acct_Balance starting at the current period to get the balance to date. Fact_Acct_Sum (our equivalent) is updated while posting documents. This is most efficient for batch posting, but is still efficient for individual documents. It can be rebuilt from Fact_Acct, of course, in case of problems. Only the period 0 records (starting balances = SB) need to be rebuilt for Fiscal years following any open periods. And that rebuilding is very fast because it starts with the SBs of that fiscal year and only needs to summarize totals by fiscal period.
An additional optimization is to have versions of Fact_Acct_Sum that leave out certain kinds of detail. For instance, a version without Product ID. These are used for reports that don't include Product ID. This is configurable, and each additional *_Sum table increases the posting cost.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Logged In: YES
user_id=1597230
In my case this report is based on a table rather than on a
View so every you ask for a financial report. Compiere
creates the temporal table for this report, that's the
reason why it takes so long.
Logged In: YES
user_id=1180760
Originator: NO
Hi Armen, I found a workaround, before you run the Financial Report, you can run the process "Performance Analysis > Accounting Rules > Update Accounting Balance" with parameter "Recreate Data" checked.
This process is very fast compared to the "Update Balances" on Financial Report (because only delete/insert, instead of update) and even is more secure, because the "Update Balances" on Financial Report doesn't delete from temporary tables the accounting entries deleted.
So, after you recreated the balances, you can run financial reports with parameter "Update Balances" unchecked and they are very fast.
Please tell me if it works, maybe we can change the Financial Report process to use the "Recreate Data" always instead of trying to update every time the table.
Regards,
Carlos Ruiz
Logged In: YES
user_id=1599854
Originator: NO
Implemented carlor suggestion in revision 970
Logged In: YES
user_id=1179440
Originator: YES
Hi Carlos,
Actually that's what I did all this time. I made the parameter always "True" in the code.
Yes they're faster but not that fast we are expecting for a robust accounting software.
I hope there are another workaround possible. Anyway, thank you for taking care this tracker.
Keep the good work and spirit.
Armen
Logged In: YES
user_id=1312539
Originator: NO
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).
Logged In: YES
user_id=1599854
Originator: NO
reopen as victor have propose a better solution
Logged In: YES
user_id=1597230
Originator: NO
File Added: rv_fact_acct.sql
View
Logged In: YES
user_id=332830
Originator: NO
ok solve in http://sourceforge.net/tracker/index.php?func=detail&aid=1642765&group_id=176962&atid=879332
kind regards
Victor Perez
Logged In: YES
user_id=142072
Originator: NO
I will describe how our legacy system provides instant financials with hundreds of thousands of LEDGER_ACCT_DAILY records (the equivalent of Fact_Acct_Balance) and millions of LEDGER_DETAIL records (the equivalent of Fact_acct). There are several levels of summary.
First, dates are converted to fiscal period = fiscal year * 100 + period. This could be Fact_Acct_Sum. In addition, period is 1 origin, and the summary table uses period 0 for starting balances for the fiscal year. Now, to compute the balance at a given date, you start at period 0 of that fiscal year in Fact_Acct_sum (start at 1 for YTD balance), and sum through the previous whole period. Then you sum through Fact_Acct_Balance starting at the current period to get the balance to date. Fact_Acct_Sum (our equivalent) is updated while posting documents. This is most efficient for batch posting, but is still efficient for individual documents. It can be rebuilt from Fact_Acct, of course, in case of problems. Only the period 0 records (starting balances = SB) need to be rebuilt for Fiscal years following any open periods. And that rebuilding is very fast because it starts with the SBs of that fiscal year and only needs to summarize totals by fiscal period.
An additional optimization is to have versions of Fact_Acct_Sum that leave out certain kinds of detail. For instance, a version without Product ID. These are used for reports that don't include Product ID. This is configurable, and each additional *_Sum table increases the posting cost.