details: /erp/devel/pi/rev/d7482229a1ec
changeset: 7914:d7482229a1ec
user: Stefan Hühner <stefan.huehner <at> openbravo.com>
date: Thu Jul 22 16:05:00 2010 +0200
summary: 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.
diffstat:
src-db/database/model/functions/FACT_ACCT_RESET.xml | 4 +++-
src-db/database/model/tables/C_PERIODCONTROL.xml | 3 +++
2 files changed, 6 insertions(+), 1 deletions(-)
diffs (41 lines):
diff -r fa203c41fbfd -r d7482229a1ec
src-db/database/model/functions/FACT_ACCT_RESET.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
diff -r fa203c41fbfd -r d7482229a1ec
src-db/database/model/tables/C_PERIODCONTROL.xml
--- 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>
------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
