Hierarchical dataset for improving performance of computing reports

Multi-level reports are common forms of reports, such as grouping reports and primary and subreports. The associated operations (group and details, master table and sub-table) must be completed on the report end because they are hierarchical and cannot be completed directly in the database. However, the report end generally only uses the sorting and traversal methods for association, with poor performance. You can use the hierarchical dataset of the rundry set to calculate a report to complete associated computing during the data source computation process, and the hierarchical result set is directly transmitted to the report for presentation, make full use of the efficient algorithm of the Set calculator in associated computing to achieve the goal of optimizing performance.

The following describes how to use the primary report and subreport.

Report description

Use the order table and order list to query the details of each order and the Order details under this Order. The report format is as follows:

Environment and data description

Test Model: Dell target 3420

CPU: Intel Core i5-3210M @ 2.50 GHz * 4

Ram: 4G

HDD: West digital WDC (500g 5400 rpm)

Operating System: Win7 (x64) SP1

JDK1.6

Database:HSQLDB

Report version5.0

 

Table Data

Write computing scripts

First, use the preset set computing tool (specifically used for data computing and a tool that provides data source support for reports) to compile the computing script (orders. DFX) to complete data calculation and return the hierarchical Dataset:

A1: connect to the data source;

A2-A3: Query order and order list data separately;

A4: Associate orders with order details by order number;

A5: return the result set with the order number not empty for the report.

Prepare reports

After creating a report template, select "set calculator" for the dataset, and specify the DFX file in the dataset editing window to complete the dataset creation.

Hierarchical dataset effect in reports

Set the report template expression:

Different from association in reports, you can use a hierarchical dataset to create a primary and subreport by using a result set returned by the Set Computing script. This improves performance, the following describes how to directly associate data in a report:

Implementation without hierarchical Dataset

Dataset

Ds1: Select order ID, order ID order number, delivery date, arrival date, customer ID, goods owner name, cargo carrier, freight fee, goods owner city from order;

DS2: Select * From Order details;

 

Report Template

Effect

Here we compare the time overhead after the hierarchical dataset (associated in the report). The following table shows the running result data units: seconds.

You can see the effect of using a hierarchical dataset. Because regular primary and subtables must use Traversal Algorithms only when the report is associated (only the associated subrecords can be searched for a single primary record ), therefore, the efficiency is not high. The set operator adopts a more efficient hash Association Scheme (all subrecords can be hash to the master record according to the corresponding code in advance, and the switch function in the Code uses the hash Association technology, the single join time can be 5-10 times faster), greatly improving the data source preparation (data computing) Efficiency of the master table and sub-table. Therefore, the hierarchical scheme can achieve higher overall computing performance.

Hierarchical dataset for improving performance of computing reports