Difference between revisions of "Very Slow Reports"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
(Added Links to manual pages)
(Improve time to generate Financial Report by removing null account elements)
Line 1: Line 1:
 
==Improve time to generate Financial Report by removing null account elements==
 
==Improve time to generate Financial Report by removing null account elements==
  
Summary:
+
'''Summary''':
  
 
The system's time to calculate and display a financial report (e.g., Balance Sheet, Income Statement) can be improved by deleting empty "Record Source" entries that contain no "Account Element" value.
 
The system's time to calculate and display a financial report (e.g., Balance Sheet, Income Statement) can be improved by deleting empty "Record Source" entries that contain no "Account Element" value.
  
Overview:
+
'''Overview''':
  
 
In this context, a financial report refers to one that is generated from [[ManPageW_FinancialReport|Financial Report]] window. A financial report is composed of 2 elements:
 
In this context, a financial report refers to one that is generated from [[ManPageW_FinancialReport|Financial Report]] window. A financial report is composed of 2 elements:

Revision as of 05:09, 22 January 2007

Improve time to generate Financial Report by removing null account elements

Summary:

The system's time to calculate and display a financial report (e.g., Balance Sheet, Income Statement) can be improved by deleting empty "Record Source" entries that contain no "Account Element" value.

Overview:

In this context, a financial report refers to one that is generated from Financial Report window. A financial report is composed of 2 elements:

1. Report Line Set which contains row data (i.e., account elements)

2. Report Column Set which contains column definition (e.g., total amount, debit, credit, etc.)

Row data in a report line set can be created by importing account elements via Import Report Line Set. When this method is used, entries are created in “Record Line” and “Record Source” tabs in Report Line Set window. However, in instances where a row data is defined to be a heading/label (i.e., it does not map to an account element), the import process creates an entry in the “Record Source” tab with an empty value in “Account Element” field. Removing these empty references has been shown to significantly improve the time it takes for the system to generate the financial report.

SQL to check if there are Report Source lines that contain no Account Element value:

 SELECT * 
 FROM PA_ReportSource 
 WHERE PA_ReportLine_ID IN(Select PA_ReportLine_ID from PA_ReportLine) 
 AND C_ElementValue_ID IS NULL;

SQL to delete Report Source lines that contain no Account Element value:

 DELETE PA_ReportSource 
 WHERE PA_ReportLine_ID IN(Select PA_ReportLine_ID from PA_ReportLine) 
 AND C_ElementValue_ID IS NULL;

-- SusyOngko - 21 Jan 2007