0002316: General Accounting Report very slow in postgres

Description

General Accounting Report is very slow in postgres.

It's supposed to be the "selectPyG" in the GeneralAccountingReportsData file that is:

  <SqlMethod name="selectPyG" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql><![CDATA[
      SELECT COALESCE(SUM(AMTACCTCR-AMTACCTDR), 0) AS NAME
      FROM FACT_ACCT, C_PERIOD, C_YEAR
      WHERE FACT_ACCT.C_PERIOD_ID = C_PERIOD.C_PERIOD_ID
      AND C_PERIOD.C_YEAR_ID = C_YEAR.C_YEAR_ID
      AND ACCOUNT_ID IN (SELECT ACCOUNT_ID
              FROM C_ELEMENTVALUE, C_VALIDCOMBINATION
              WHERE C_VALIDCOMBINATION.ACCOUNT_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID
              AND C_ELEMENTVALUE.ACCOUNTTYPE = ?)
      AND FACT_ACCT.FACTACCTTYPE <> 'R'
      AND 1=1
      AND C_YEAR.YEAR = ?
      AND FACT_ACCT.AD_ORG_ID IN ('1')
      ]]></Sql>
        <Parameter name="accountType"/>
        <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT >= TO_DATE(?)]]></Parameter>
        <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT < TO_DATE(?)]]></Parameter>
        <Parameter name="year"/>
        <Parameter name="adOrgId" optional="true" type="replace" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
   </SqlMethod>

and it would work better if it was:

  <SqlMethod name="selectPyG" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql><![CDATA[
      SELECT COALESCE(SUM(AMTACCTCR-AMTACCTDR), 0) AS NAME
      FROM FACT_ACCT, C_PERIOD, C_YEAR, (SELECT ACCOUNT_ID
              FROM C_ELEMENTVALUE, C_VALIDCOMBINATION
              WHERE C_VALIDCOMBINATION.ACCOUNT_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID
              AND C_ELEMENTVALUE.ACCOUNTTYPE = ?) AA
      WHERE FACT_ACCT.C_PERIOD_ID = C_PERIOD.C_PERIOD_ID
      AND C_PERIOD.C_YEAR_ID = C_YEAR.C_YEAR_ID
      AND FACT_ACCT.ACCOUNT_ID = AA.ACCOUNT_ID
      AND FACT_ACCT.FACTACCTTYPE <> 'R'
      AND 1=1
      AND C_YEAR.YEAR = ?
      AND FACT_ACCT.AD_ORG_ID IN ('1')
      ]]></Sql>
        <Parameter name="accountType"/>
        <Parameter name="dateFrom" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT >= TO_DATE(?)]]></Parameter>
        <Parameter name="dateTo" optional="true" after="AND 1=1"><![CDATA[ AND FACT_ACCT.DATEACCT < TO_DATE(?)]]></Parameter>
        <Parameter name="year"/>
        <Parameter name="adOrgId" optional="true" type="replace" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/>
   </SqlMethod>

Carlos Romero
Openbravo Team

Notes
(0002723) cromero (viewer) 2008-03-05 17:23 edited on: 2008-06-12 09:23	Logged In: YES user_id=1500614 Originator: YES Fixed in revision 2672

(0005905) user71 2005-06-01 00:00 edited on: 2008-06-12 09:43	This bug was originally reported in SourceForge bug tracker and then migrated to Mantis. You can see the original bug report in: https://sourceforge.net/support/tracker.php?aid=1839432 [^]

Issue History
Date Modified	Username	Field	Change

Relationships [ Relation Graph ] [ Dependency Graph ]