0031942: UpdateCustomerBalance modulescript is quite slow

Description

UpdateCustomerBalance modulescript's execution is slow with high volumes and can be easily improved.

There is a select that generates a list of business partners to loop and then a loop that executes an update for each iteration. This can be improved changing it for a unique update (See proposed solution).

Steps To Reproduce

Execute install.source with a sampledata with a high volume of business partners and see the execution time of this modulescript.

Proposed Solution

The following code:

UpdateCustomerBalanceData[] data = UpdateCustomerBalanceData.calculateCustomerCredit(cp);
for (UpdateCustomerBalanceData ucb : data) {
   UpdateCustomerBalanceData.updateCustomerCredit(cp, ucb.customercredit, ucb.cBpartnerId);
}

Can be changed for the following update:

update c_bpartner
set so_creditused = customercredit, updatedby='0', updated=now()
from
(SELECT A.c_bpartner_id, SUM(A.amount) as customercredit
        FROM (SELECT c_bpartner_id, COALESCE(SUM(ps.outstandingamt * (CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END)), 0) as amount
              FROM fin_payment_schedule ps join c_invoice inv on (ps.c_invoice_id = inv.c_invoice_id)
              WHERE ps.outstandingamt <> 0
              GROUP BY c_bpartner_id
              UNION ALL
              SELECT p.c_bpartner_id, COALESCE(SUM((p.generated_credit - p.used_credit) * (CASE WHEN p.isreceipt = 'Y' THEN -1 ELSE 1 END)), 0) as amount
              FROM FIN_PAYMENT p
              WHERE p.c_bpartner_id is not null
                    AND (p.generated_credit - p.used_credit) <> 0
                    AND p.generated_credit <> 0
                    AND p.processed = 'Y'
              GROUP BY p.c_bpartner_id) A
        GROUP BY A.c_bpartner_id) B
where c_bpartner.c_bpartner_id = b.c_bpartner_id;

Issue History
Date Modified	Username	Field	Change
2016-01-19 13:28	ioritzCia	New Issue
2016-01-19 13:28	ioritzCia	Assigned To	=> Triage Finance
2016-01-19 13:28	ioritzCia	Modules	=> Advanced Payables and Receivables Mngmt
2016-01-19 13:28	ioritzCia	Triggers an Emergency Pack	=> No
2016-01-19 13:29	ioritzCia	Tag Attached: Performance

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.