[Requesting Help] Optimised approach to create Chart of Accounts

ERPNext Accounting
1

Hello,

We would like to go for a better approach to create the chart of accounts on our ERPNext instance specific to our use case. Any help will be greatly appreciated.

Use Case:

For auditing purpose, a company is created for each of the clients. For a new company created (a few are a multi-level company), we create the chart of accounts as per their model from the base JSON and add the additional ledgers (accounts) to it.

On adding clients to the system, our chart of accounts creation started taking more time and is becoming regressive day by day. We have about 96957 entries in the accounts list in one of our instances.

As Frappe uses NestedSet to form the tree structure, on the database processlist, we could notice the update tabAccount on lft and rgt taking a long time. For the creation of a COA with 44 accounts, it updated 16533 records in the tabAccount table. It took about 34 minutes.

Subsequently, this resulted in Lock wait timeouts and sometimes deadlocks. All the recommended configurations for the DB Server is applied. It is a 16GB 4 vcores managed DB machine from RDS.

Can someone suggest a better approach here in terms of the time taken (minimal updates) and the performance (avoiding lock waits)?

Minor related Findings:

  1. tabAccount is not having an index on company that becomes fundamental in our use case.
  2. Will the insertion of accounts such that its to be created lft or rgt in any sorted order help decreasing the number of records updated in the tree?

Thanks in advance

1 Like
2

@nabinhait, @vkcr

It will be helpful if you can share your comments on optimizing the COA creation process for multiple companies on the same site use cases.

1 Like
3

Hi ERPNext Team, any update on this. This issue is a major performance bottleneck, because in such scenario every record in tabAccount table is updated. Can we limit the updates only to the account of a specific company, instead of updating tabAccount record of each and every company?

4

Hey anyone found solution?