Improve performance on Budget valiation

[ruthra-kumar]

Issue

The current implementation of budget validation needs some refactor. On large sites, Purchase Orders with 100 items timeout while submitting.

Bottlenecks

Unavoidable nested-loop

Purchase Order can have any number of Items 'I', each item can have any number of dimensions 'D', and each dimension can have budgets for different fiscal years 'B'.

This structure lends itself to be handled by nested-loop - (I x D x B), like so

for x in item / gl entry:
 for dim in all_dimensions:
  for budget in all_budgets:
   calculate_and_validate_expenses()

Methods are too generic

validate_expense_against_budget() is too generic. It implicitly handles individual GL Entries and 'Items' (from Purchase Order) passed as args.

erpnext/erpnext/accounts/doctype/budget/budget.py

Line 143 in c7d6923

def validate_expense_against_budget(args, expense_amount=0):

[miteshpc]

Maybe the logic used for making Trial Balance work with large memory footprint data, can work here too.

[ruthra-kumar]

Maybe the logic used for making Trial Balance work with large memory footprint data, can work here too.

You mean the 'Account Closing Balance' feature?

[ruthra-kumar]

For Purchase Order, there are some

1. Unlike GL Entry, PO has all the 'Items' and dimensions already available while validating. Deduplication can be done beforehand. Only validate for each unique key - (expense_account, dimension)
2. Cache the results for each key - (expense_account, dimension) - new method with clear parameters

[miteshpc]

#1 sounds just fine. That will help to move through that list without much pending. The results can be resulting into a list of items that are contributing towards the violation of budget control.

[miteshpc]

Somewhere down the line we may need a Budget Usage Ledger doctype and a Budget Closing Balance doctype