Fixes issue 30584: Performance problem in InitializeBPCurrency modulescript
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Fri, 14 Aug 2015 13:02:06 +0200
changeset 27765 342d80ab4640
parent 27573 828faf193994
child 27766 21aab95f70ec
Fixes issue 30584: Performance problem in InitializeBPCurrency modulescript

Improve performance of UpdateBpCurrency method in InitializeBPCurrency modulescript.
src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrencyData.class
src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java
src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql
Binary file src-util/modulescript/build/classes/org/openbravo/modulescript/InitializeBPCurrencyData.class has changed
--- a/src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java	Thu Aug 13 04:34:37 2015 +0000
+++ b/src-util/modulescript/build/javasqlc/src/org/openbravo/modulescript/InitializeBPCurrencyData.java	Fri Aug 14 13:02:06 2015 +0200
@@ -126,15 +126,21 @@
     String strSql = "";
     strSql = strSql + 
       "        UPDATE C_Bpartner bp" +
-      " 		SET BP_Currency_ID = (" +
-      " 		                     SELECT DISTINCT i.C_Currency_ID" +
-      " 		                     FROM C_INVOICE i" +
-      " 		                     WHERE i.C_Bpartner_ID = bp.C_Bpartner_ID " +
-      " 		                     AND i.docstatus = 'CO'" +
-      " 		                     AND ((SELECT COUNT(DISTINCT i.C_CURRENCY_ID) FROM C_INVOICE i " +
-      " 		                     WHERE i.docstatus='CO' AND i.C_Bpartner_ID = bp.C_Bpartner_ID) = 1) " +
-      " 		                     )" +
-      " 		WHERE bp.BP_Currency_ID IS NULL";
+      "        SET BP_Currency_ID = (" +
+      "            SELECT max(i.c_currency_id)" +
+      "            FROM C_INVOICE i" +
+      "            WHERE i.c_bpartner_id = bp.c_bpartner_id" +
+      "            AND i.docstatus = 'CO'" +
+      "            GROUP BY i.c_bpartner_id" +
+      "            HAVING count(distinct i.c_currency_id) = 1 " +
+      "        )" +
+      "        WHERE bp.BP_Currency_ID IS NULL" +
+      "        AND exists (" +
+      "            SELECT 1 " +
+      "            FROM c_invoice ie " +
+      "            WHERE ie.c_bpartner_id = bp.c_bpartner_id " +
+      "            AND ie.docstatus  = 'CO'" +
+      "        )";
 
     int updateCount = 0;
     PreparedStatement st = null;
--- a/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql	Thu Aug 13 04:34:37 2015 +0000
+++ b/src-util/modulescript/src/org/openbravo/modulescript/InitializeBPCurrency_data.xsql	Fri Aug 14 13:02:06 2015 +0200
@@ -12,7 +12,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2014 Openbravo SLU
+ * All portions are Copyright (C) 2014-2015 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -49,15 +49,21 @@
     <Sql>
      <![CDATA[
         UPDATE C_Bpartner bp
- 		SET BP_Currency_ID = (
- 		                     SELECT DISTINCT i.C_Currency_ID
- 		                     FROM C_INVOICE i
- 		                     WHERE i.C_Bpartner_ID = bp.C_Bpartner_ID 
- 		                     AND i.docstatus = 'CO'
- 		                     AND ((SELECT COUNT(DISTINCT i.C_CURRENCY_ID) FROM C_INVOICE i 
- 		                     WHERE i.docstatus='CO' AND i.C_Bpartner_ID = bp.C_Bpartner_ID) = 1) 
- 		                     )
- 		WHERE bp.BP_Currency_ID IS NULL
+        SET BP_Currency_ID = (
+            SELECT max(i.c_currency_id)
+            FROM C_INVOICE i
+            WHERE i.c_bpartner_id = bp.c_bpartner_id
+            AND i.docstatus = 'CO'
+            GROUP BY i.c_bpartner_id
+            HAVING count(distinct i.c_currency_id) = 1 
+        )
+        WHERE bp.BP_Currency_ID IS NULL
+        AND exists (
+            SELECT 1 
+            FROM c_invoice ie 
+            WHERE ie.c_bpartner_id = bp.c_bpartner_id 
+            AND ie.docstatus  = 'CO'
+        )
       ]]>
     </Sql>
   </SqlMethod>