Monthly sales calculation on every Sales Invoice is slowing down ErpNext

We are running ErpNext where 100s of invoices are generated per minute. As per the code in sales_invoice.py update_company_current_month_sales () method is called on_submit and on_cancel of the sales invoices.

The update_company_current_month_sales() is as follows

def update_current_month_sales(self):
		if frappe.flags.in_test:
			update_company_current_month_sales(self.company)
		else:
			frappe.enqueue('erpnext.setup.doctype.company.company.update_company_current_month_sales',
				company=self.company)

although the function runs as a background job, It reads all the sales invoices of the current company and then calculates the monthly sales.

def update_company_current_month_sales(company):
	current_month_year = formatdate(today(), "MM-yyyy")

	results = frappe.db.sql('''
		select
			sum(base_grand_total) as total, date_format(posting_date, '%m-%Y') as month_year
		from
			`tabSales Invoice`
		where
			date_format(posting_date, '%m-%Y')="{0}"
			and docstatus = 1
			and company = "{1}"
		group by
			month_year
	'''.format(current_month_year, frappe.db.escape(company)), as_dict = True)

	monthly_total = results[0]['total'] if len(results) > 0 else 0

	frappe.db.set_value("Company", company, "total_monthly_sales", monthly_total)
	frappe.db.commit()

We think that doing this on every invoice update would degrade the overall performance. In case there are some back dated sales invoice entries made, ErpNext by default updates all the ledgers and invoices posted after that resulting in company’s monthly sale to be calculated for each invoice getting updated.

Instead of calculating monthly sale immediately after each invoice updation, we can do the same as a scheduled job running every hour or at the end of the day. We believe this would reduce the load on the system significantly.

What does the community think?

1 Like

Normal use case of small businesses requires creation of invoices online and they mostly do it one at a time. In case of automated invoice creation in a use case of yours you can run a background job.

In case you wish to contribute your code, you can make a configurable setting by providing a check box in “Selling Settings” saying “Schedule Monthly Sales Update” and try to contribute your code via a pull request.

In case calling update_company_current_month_sales on invoice creation is a performance issue, there is incidentally a sales stats update every day, via the daily scheduled function cache_companies_monthly_sales_history:

which calculates and updates monthly history. A plausible location to update current month sales instead of invoice creation.

Okay. As of now we have commented the code to calculate Project and Company level sales in the same transaction as invoice.

As @Pawan suggested, we can make a configuration in ‘selling settings’ and send pull request.

Opened a PR based on above suggestions => https://github.com/frappe/erpnext/pull/14670.

1 Like