Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0031942Openbravo ERP09. Financial managementpublic2016-01-19 13:282016-01-19 13:29
ReporterioritzCia 
Assigned ToTriage Omni OMS 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesAdvanced Payables and Receivables Mngmt
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0031942: UpdateCustomerBalance modulescript is quite slow
DescriptionUpdateCustomerBalance 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 ReproduceExecute install.source with a sampledata with a high volume of business partners and see the execution time of this modulescript.
Proposed SolutionThe 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;
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-01-19 13:28ioritzCiaNew Issue
2016-01-19 13:28ioritzCiaAssigned To => Triage Finance
2016-01-19 13:28ioritzCiaModules => Advanced Payables and Receivables Mngmt
2016-01-19 13:28ioritzCiaTriggers an Emergency Pack => No
2016-01-19 13:29ioritzCiaTag Attached: Performance

There are no notes attached to this issue.