I notice that this function get_balance_on() (in erpnext.account.utils) is called on multiple place in ERPNext, now, this function actually do a sum of debit - sum of credit, overtime, this is very heavy. I don’t think accounting period closing help. If I look at the code its not looking into period closing at all, but I may be mistaken… The thing is, when the number of records become huge, the DB is hammered pretty hard. This function is also called multiple times in many actions.
Is there any settings to prevent this function to scan the entire GL Entry table, instead, getting the last summarized total for records from different fiscal year?
Will greatly appreciate performance tips and tricks on this area.
Unfortunately not. Probably the last resort is to delete old data and create opening balance, but that requires a lot of effort. Unless someone ini the community or in the core team can suggest a best practice to do this.
Really there should be a summary table that holds those balances so that we don’t need to perform the sum and subtraction operations.
We have more than 1M sales invoice at the moment and more on other transactions.
Running for more than 3 years now.
There’s got to be some way to optimize this. Thinking of archiving old data, but I’m finding to see if there’s a recommended way for ERPNext. Other systems that I have seen, like SAP can archive old transactions, but they have their own recommended ways.