Fixed issue 25278: Long time to update a warehouse with thousends of locators
authorJorge Garcia <jorge.garcia@openbravo.com>
Wed, 20 May 2015 18:17:08 +0200
changeset 26819 f89eff26d185
parent 26818 9eb109c1f121
child 26820 ef5da8dc7f38
Fixed issue 25278: Long time to update a warehouse with thousends of locators

On an environment with 60.000 locators for one of the warehouse, when updating
a field of the warehouse it takes a long time to save the record.

Part of the problem is the update of the m_warehouse table. When updated, the
trigger launch an update of the organization id for M_LOCATOR and
M_WAREHOUSE_ACCT tables.

The solution is to check if the organization has changed and, if not, avoid the
unnecessary update of those tables.
src-db/database/model/triggers/M_WAREHOUSE_TRG.xml
--- a/src-db/database/model/triggers/M_WAREHOUSE_TRG.xml	Wed May 27 16:31:12 2015 +0200
+++ b/src-db/database/model/triggers/M_WAREHOUSE_TRG.xml	Wed May 20 18:17:08 2015 +0200
@@ -72,11 +72,13 @@
         ;
     END LOOP;
   ELSIF (UPDATING) THEN
-    UPDATE M_WAREHOUSE_ACCT SET AD_ORG_ID = :new.AD_ORG_ID
-    WHERE M_WAREHOUSE_ID = :new.M_WAREHOUSE_ID;
+    IF (:new.AD_ORG_ID <> :old.AD_ORG_ID) THEN
+      UPDATE M_WAREHOUSE_ACCT SET AD_ORG_ID = :new.AD_ORG_ID
+      WHERE M_WAREHOUSE_ID = :new.M_WAREHOUSE_ID;
     
-    UPDATE M_LOCATOR SET AD_ORG_ID = :new.AD_ORG_ID
-    WHERE M_WAREHOUSE_ID = :new.M_WAREHOUSE_ID;
+      UPDATE M_LOCATOR SET AD_ORG_ID = :new.AD_ORG_ID
+      WHERE M_WAREHOUSE_ID = :new.M_WAREHOUSE_ID;
+      END IF;
   END IF;
 END M_WAREHOUSE_TRG
 ]]></body>