Hi,
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("""
select
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 payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_entry
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
{condition}
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(
dr_or_cr=dr_or_cr,
invoice = invoice,
payment_dr_or_cr=payment_dr_or_cr,
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