Fixed 14035: fact_acct_reset is too slow
authorStefan Hühner <stefan.huehner@openbravo.com>
Thu, 22 Jul 2010 16:05:00 +0200
changeset 7914 d7482229a1ec
parent 7913 fa203c41fbfd
child 7915 11d7faf63841
Fixed 14035: fact_acct_reset is too slow
Speed up this pl-function by:
- add missing index to c_periodcontrol table
- move one function call out of a complex query as it does not depend on any
data from the query, instead execute the function onces before the bigger
query.
src-db/database/model/functions/FACT_ACCT_RESET.xml
src-db/database/model/tables/C_PERIODCONTROL.xml
--- a/src-db/database/model/functions/FACT_ACCT_RESET.xml	Fri Jul 23 13:03:10 2010 +0200
+++ b/src-db/database/model/functions/FACT_ACCT_RESET.xml	Thu Jul 22 16:05:00 2010 +0200
@@ -41,6 +41,7 @@
   v_DateColumn VARCHAR2(2000):='';
   v_AD_Org_ID VARCHAR2(32);
   v_TableName VARCHAR2(48):='';
+  v_AD_Org_CalOwner VARCHAR(32);
   -- Parameter
   TYPE RECORD IS REF CURSOR;
     Cur_Parameter RECORD;
@@ -207,6 +208,7 @@
           -- Update Table
           v_ResultStr:='ResetTable:' || Cur_Tables.TableName;
            IF(v_DeletePosting='Y') THEN
+            v_AD_Org_CalOwner := AD_ORG_GETCALENDAROWNER(v_AD_Org_ID);
             FOR Cur_Fact_Acct IN (
               SELECT DISTINCT Record_ID
               FROM FACT_ACCT
@@ -226,7 +228,7 @@
                       AND fa_aux.AD_TABLE_ID = FACT_ACCT.AD_TABLE_ID
                       AND fa_aux.RECORD_ID = FACT_ACCT.RECORD_ID
                       AND C_PeriodControl.AD_Org_ID=AD_Org.AD_Org_ID
-                      AND C_PeriodControl.AD_Org_ID=AD_ORG_GETCALENDAROWNER(v_AD_Org_ID)
+                      AND C_PeriodControl.AD_Org_ID=v_AD_Org_CalOwner
                     )
                 AND Record_ID=(
                       CASE COALESCE(v_Record_ID, '0') WHEN '0' THEN Record_ID ELSE v_Record_ID
--- a/src-db/database/model/tables/C_PERIODCONTROL.xml	Fri Jul 23 13:03:10 2010 +0200
+++ b/src-db/database/model/tables/C_PERIODCONTROL.xml	Thu Jul 22 16:05:00 2010 +0200
@@ -62,6 +62,9 @@
       <foreign-key foreignTable="AD_ORG" name="C_PERIODCONTROL_AD_ORG">
         <reference local="AD_ORG_ID" foreign="AD_ORG_ID"/>
       </foreign-key>
+      <index name="C_PERIODCONTROL_PERIOD" unique="false">
+        <index-column name="C_PERIOD_ID"/>
+      </index>
       <check name="C_PERIODCONTROL_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check>
     </table>
   </database>