Slow query Get Outstanding Invoices in Payment Entry


Using v10.1.15

I found this query under erpnext.accounts.utils.get_outstanding_invoices needs to be optimized. Its very slow if party has many records because it populate all tabGL Entry records using subqueries to get the outstanding balance… in my case it took MINUTES to execute this query

invoice_list = frappe.db.sql("""
			voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
				select ifnull(sum({payment_dr_or_cr}), 0)
				from `tabGL Entry` payment_gl_entry
				where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
					and if(invoice_gl_entry.voucher_type='Journal Entry',
						payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no,
						payment_gl_entry.against_voucher = invoice_gl_entry.against_voucher)
					and payment_gl_entry.party_type = invoice_gl_entry.party_type
					and =
					and payment_gl_entry.account = invoice_gl_entry.account
					and {payment_dr_or_cr} > 0
			) as payment_amount
			`tabGL Entry` invoice_gl_entry
			party_type = %(party_type)s and party = %(party)s
			and account = %(account)s and {dr_or_cr} > 0
			and ((voucher_type = 'Journal Entry'
					and (against_voucher = '' or against_voucher is null))
				or (voucher_type not in ('Journal Entry', 'Payment Entry')))
		group by voucher_type, voucher_no
		having (invoice_amount - payment_amount) > 0.005
		order by posting_date, name""".format(
			invoice = invoice,
			condition=condition or ""
		), {
			"party_type": party_type,
			"party": party,
			"account": account,
		}, as_dict=True)

For now I simplify this to get directly from Sales/Purchase Invoice table to get outstanding amounts…this reduce the query from minutes to milliseconds…

But it will be more nice if we can do it with 1 query to included outstanding journal entry too…
So above original query needs to be fixed. The subquery is the root cause I think…Any body can give suggestion for 1 sql query without using subquery to get all outstanding invoices & journal entry

1 Like


Will be great if you can change the pull request for the optimization. That’s how the open source project from the community contribution.

For now, @nabinhait please check if this makes sense.