Balance calculation on Payment Entry and Journal Entry causes performance impacts

On save/submit of either Journal Entry, or Payment Entry, a query is run which sums up balance of the account and party. This query reads each and every entry belonging to account, something which is expected to increase with time, hence will cause JV and PE creation to slow down, with time (Imagine billions of transactions as seen with zerodha).

There seem to be 3 solutions (IMHO).

  1. Calculate balance in GL Entry after each transaction (non-scalable approach, transactions won’t be able to run concurrently, as they’ll be forced to run serially)
  2. Maintain a bin-like doctype for Account Balance (Again, every transaction will have to lock this document to update it, hence same problem as above)
  3. Remove balance calculation from PE and JV.

I support 3rd as balance is not really needed from PE. Any cancellation of previous PE will not update all future PEs, hence it’s only an indicative number, not actual. And, there is always awesome report called “General Ledger” if someone wants to view balance. That way, this costly calculation be only be done on demand, not always. And we can always optimise the ways to view balance in future in that report itself.

@rmehta @nabin What are your views??

6 Likes

Yes some caching of balances seems like a good idea. If we have immutable ledger, then we can maintain the balance in the ledger entry itself.

Why not simply remove the calculation altogether?

@rmehta May be we can remove the balance field as well. I really don’t think users refer balances while doing journal entry.
May be @AccountingTeam can show some light on the case.

1 Like

Does someone monitor @AccountingTeam account on discuss??

Btw our team did some analysis on the code and found out the balance is purely for view right now. Removing it won’t impact any functionality.