Several Factors Affect Report Performance

In software projects users often complain that the report and query is too slow to be accepted. This brings great pressure to the developers.So, what are the main steps in report production process? What stages of this process may suffer from performance issues? How to optimize these stages?

Let's look the steps for report processing, which is shown below:

Figure 1：Report handling process illustration

As shown in the above figure,the report process is:

1. The user selectsparameters for report, and the report engine will interpret the report according to the report template and input parameters. Requests for data computation and retrieval are then sent to data layers.

2. The data layer is responsible for data retrieval, computation and the return of the data. Data layers are normally traditional databases: Oracle, DB2, etc.

3.  The data layer pass the data result, through interfaces like JDBC, to reporting tools.

4.  Reporting toolspresents to users the data they received with visual reports, according to the report template & styles required.

During the whole process of report preparation, each stage in the diagram, report layer and data layer can become a performance bottleneck.

One thing worth to discuss is that in Figure 1, the stage"R2 generate visual report" normally includes some data computation process: data returned from the data layer has to be further processed to meet the report requirement.It’s especially true for those reporting tools with programming capabilities that this stage will consume some computation time.For example: the the custom scripts for BIRT, custom dataset for Runqian Report, and inter-cell computation.

Therefore, we can separate this stage, which is good for us to analyze the factors affecting the performance of the report. After being separated,the process is as below:

 

Figure 2 Report handling process illustration after being further separated

In Figure 2, the highlighted stagesare the main reasons for performance degradation. Obviously, the interpretation of report by the report engine, and the presentation of the report seldom become the key factor for performance issues. Instead, data computation, as well as JDBC data transmission is normally the root of such issues. Therefore, optimization on data computation or JDBC transmission is a more rewarding approach.
As we are clear with the report generation, let’s now deep dig for reasons in the highlighted stages of Figure 2, which are causing the performance issues. 

I.The data layer"D1 executing data computation"stage.

In development practices, report developers often need to write very long and complex SQL statements (or stored procedure) to prepare data for the report.

Developers usually find that it’s difficult to implement complex computation with SQL (or stored procedures), especially when they are related to ordered sets. For example, when computing for products, which ranked in the monthly top 10 products lists, or stocks, whose price rise for last five consecutive days. The SQL statement to prepare data for these report are too complicated and may run for tens or even hundreds of lines.

Since it’s difficult to manually specify the full execution path for the database, the execution of complex SQL statements are often slow. It’s hard for developers to manually optimize these complex SQL statements. This is the main factor that affects the performance of "D1 executing data computation" stage.

Example: Computing for customers who rank in Top 10 revenue contributor in every month.

select Client from(

select * from(

select B.*,row_number() over(partition by month order by SumValuedesc)

rown from(

selectto_char(SellDate,'mm') month,Client,sum(Quantity*Amount) SumValue

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

group by to_char(SellDate,'mm'),Client order by month,client ) B

)C

whererown<=10

)D

group by Client

having count(Client)=(

select  count(distinct(to_char(SellDate,'mm')))

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

)

II.The data layer "D2 JDBC data transmission" stage.

The JDBC drivers from traditional mainstream database are usually less efficient when transfer data stream into Java objects. However, they cannot be bypassed. When handling large amounts of data the performance is not acceptable. Even with new versions of databases, the problem remains. Therefore, for reports against large amount of data, JDBC is often the reason that the report is becoming too slow.

III.The report layer "R2 data re-computation"stage.

1. Many reporting tools handle " R2 data re-computation" and "R3 generating visual presentation"together. The data computation stage includes lot of display attributes(font, color, cell size, etc), which will slow down the computationspeed.

2. The Computational programming in reporting tool often includes custom functions, JavaScript or java language and does not provide complete relational data computation library. Most of the functionality requires programmers to write their own implementation. The result is high cost and lengthy development, as well as unpredictable code quality or report performance.

3. Reporting tools do not have the ability for parallel computing. This means complex task with large amount of data cannot be split into small and separated tasks to be processed by stand-alone or multiple machines.Thusit is difficult to effectively improve computing speed.

Know the causes for low performance issues existed in these stages, we can now consider the following optimization alternatives:

I.The data layer"D1 executing data computation"stage.

To cope with the difficulties associated with complex SQL statement optimization, we can use procedural programming language. In this regard, esProc is a good choice. Its code is more in line with the natural way of thinking, and isseveral times shorter than SQL when solving same issues. On the one hand, it’s easy to develop, debug and maintain, reducing development costs; on the other hand, it’s up for the programmer to select the execution path and process, which is easier to improve computing performance.

With use esProc, it’s very easy to computefor customers who rank in Top 10 revenue contributor in every month:

II.  The data layer "D2 JDBC data transmission" stage

To resolve the issue of slow data transmission with JDBC in traditional database, we can consider storing the relatively large-scale historical data in file system. It’s much faster to access data in file system through esProc, than JDBC transmission from traditional database. Although the data transmission from esProc to reporting tools is also done through JDBC, it’s much faster as esProc’s JDBC doesn’t need to convert the data stream object in any way.

Another possible solution is the use of parallel computing with several esProc subroutines to retrieve data from database through multiple JDBC connections. During actual test against Oracle we found that with 16 parallel tasks the data transmission speed can be increased by ten times. Considering the increased overhead to the database, this solution is suitable for systems where the database is less stressed.

III.  The report layer "R2 data re-computation" stage

1．esProc does not include display properties in its computation. Thus the computing speed is improved .

2. esProc comes with a rich library for relational data computation, which offers great varietyof performance optimization options. Especially when used with ordered sets, programmers can effectively leverage the regularity ofbusiness data to control the execution plan and improve computing speed.

3. esProc comes with parallel computing capabilities, with which we can split large data and more complex tasks into small parallel tasks running on multiple machines. This will effectively improve the computing speed.

Note: for specific performance test measurements, please refer to esProc’s performance test reports.