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.
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
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:
tabAccountis not having an index on
companythat becomes fundamental in our use case.
- Will the insertion of accounts such that its to be created
rgtin any sorted order help decreasing the number of records updated in the tree?
Thanks in advance