Fixes issue 30009: Performance problems in UpdatePaymentPlan modulscript
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 22 Jun 2015 18:29:20 +0200
changeset 27139 89faeb629846
parent 27138 c85e2fa57fa1
child 27140 1cc71ac627a7
Fixes issue 30009: Performance problems in UpdatePaymentPlan modulscript

UpdatePaymentPlan modulscript has been changed in order to improve the performance.
Now a select will be done to get wrong records, and then they will be updated inside a loop.
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class
src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class
src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java
src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlan.class has changed
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/UpdatePaymentPlanData.class has changed
--- a/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java	Wed Jun 17 19:17:55 2015 +0200
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan.java	Mon Jun 22 18:29:20 2015 +0200
@@ -31,7 +31,9 @@
       ConnectionProvider cp = getConnectionProvider();
       boolean isExecuted = UpdatePaymentPlanData.isExecuted(cp);
       if (!isExecuted) {
-        UpdatePaymentPlanData.update(cp);
+        for (UpdatePaymentPlanData record : UpdatePaymentPlanData.getWrongRecords(cp)) {
+          UpdatePaymentPlanData.update(cp, record.amount, record.amount, record.id);
+        }        
         UpdatePaymentPlanData.createPreference(cp);
       }
     } catch (Exception e) {
--- a/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql	Wed Jun 17 19:17:55 2015 +0200
+++ b/src-util/modulescript/src/org/openbravo/modulescript/UpdatePaymentPlan_data.xsql	Mon Jun 22 18:29:20 2015 +0200
@@ -23,29 +23,39 @@
     <SqlMethodComment></SqlMethodComment>
     <Sql>
       <![CDATA[
-      SELECT '' AS finpaymentscheduleid, '' AS paidamt FROM DUAL
+      SELECT '' AS id, '' AS amount FROM DUAL
       ]]>
     </Sql>
     <Field name="rownum" value="count"/>
   </SqlMethod>
+  <SqlMethod name="getWrongRecords" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+      <![CDATA[
+        SELECT t1.fin_payment_schedule_id as id, t2.linesamount as amount
+        FROM fin_payment_schedule t1 JOIN (
+            SELECT psd.fin_payment_schedule_order, sum(psd.amount) AS linesamount
+            FROM fin_payment_scheduledetail psd
+            WHERE psd.isinvoicepaid='Y'
+            GROUP BY psd.fin_payment_schedule_order
+        ) t2
+        ON t1.fin_payment_schedule_id = t2.fin_payment_schedule_order
+        WHERE t1.paidamt/2 = t2.linesamount
+      ]]>
+    </Sql>
+  </SqlMethod>
   <SqlMethod name="update" type="preparedStatement" return="rowCount">
     <SqlMethodComment></SqlMethodComment>
     <Sql>
       <![CDATA[
-     update fin_payment_schedule set outstandingamt = amount - (select sum(psd.amount)
-                                                                from fin_payment_scheduledetail psd
-                                                                where isinvoicepaid='Y'
-                                                                and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id),
-                                                     paidamt = (select sum(psd.amount)
-                                                                from fin_payment_scheduledetail psd
-                                                                where isinvoicepaid='Y'
-                                                                and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id)
-     WHERE fin_payment_schedule.paidamt/2 = (select sum(psd.amount)
-     from fin_payment_scheduledetail psd
-     where isinvoicepaid='Y'
-     and psd.fin_payment_schedule_order = fin_payment_schedule.fin_payment_schedule_id)
+        UPDATE fin_payment_schedule 
+        SET paidamt = to_number(?), outstandingamt = amount - to_number(?)
+        WHERE fin_payment_schedule_id = ?
       ]]>
-    </Sql>
+    </Sql>    
+    <Parameter name="amount1"/>
+    <Parameter name="amount2"/>
+    <Parameter name="id"/>
   </SqlMethod>
   <SqlMethod name="isExecuted" type="preparedStatement" return="boolean">
     <SqlMethodComment></SqlMethodComment>