0016117: Performace problem when launching update.database -Dforce=true due to the module script

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0016117

Openbravo ERP

I. Performance

public

2011-02-07 12:05

2011-03-28 16:22

Reporter

jonalegriaesarte

Assigned To

mirurita

Priority

urgent

Severity

major

Reproducibility

always

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

2.50MP26

Target Version

Fixed in Version

2.50MP28

Merge Request Status

Review Assigned To

OBNetwork customer

OBPS

Web browser

Modules

Advanced Payables and Receivables Mngmt

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0016117: Performace problem when launching update.database -Dforce=true due to the module script

Description

There is a performance problem when launching update.database -Dforce=true due to the APRM's module script.

Steps To Reproduce

The problem appears when you define a big number of organizations (400 in this case) and you open periods to each organization (the problem appears when you open 5 periods to 5 organizations). The problem is in the insertPeriod method in file modules/org.openbravo.advpaymentmngt/src-util/modulescript/src/org/openbravo/advpaymentmngt/modulescript/CreateAccountingConfiguration_data.xsql, because this method is trying to insert a big amount of data, and the select to obtain the data takes much time.

Proposed Solution

We think that the insert could be changed as follows:

INSERT INTO c_periodcontrol(
                c_periodcontrol_id, ad_client_id, ad_org_id, isactive,
     created,
                createdby, updated, updatedby, c_period_id, docbasetype,
     periodstatus,
                periodaction, processing)
     select c_period.ad_client_id as ad_client_id, a.ad_org_id, 'Y',
     now(),
          '100', now(), '100', c_period.c_period_id, ad_ref_list.value
          , 'C' as status
          ,'N', 'N'
     from c_period
     , c_year,
     (select ad_org_id from ad_org where isperiodcontrolallowed = 'Y') a,
     ad_ref_list
     where c_period.c_year_id = c_year.c_year_id
     and c_year.c_calendar_id in (select c_calendar_id from ad_org
                where ad_org_id = ad_org_getcalendarowner(a.ad_org_id))
     and ad_ref_list.isactive='Y'
     and ad_ref_list.ad_reference_id = '183'
     and not exists (select 1
                from c_periodcontrol
                where c_periodcontrol.c_period_id = c_period.c_period_id
                and c_periodcontrol.docbasetype = ad_ref_list.value
                and c_periodcontrol.ad_org_id = a.ad_org_id)
     group by c_period.c_period_id, c_period.ad_client_id,
     ad_ref_list.value,
     a.ad_org_id

Additional Information