Parallel fetch for improving the performance of computing reports in rundry Sets

Sometimes a report needs to retrieve more data from the database when it is presented or exported, while the JDBC fetch speed is always slow, which may exceed the time consumed by other report operations, the entire report generation process is extremely inefficient. Next we will look at how to improve the performance of JDBC data retrieval by using the parallel computer system of the computing report.

The so-called parallel fetch refers to the use of multithreading technology in the report tool to establish multiple connections with the database, while reading a copy of the source data, which requires the source data segment, each thread (database connection) read a portion of the content, and combine the results of all threads to obtain the total target data.

The parallel mechanism built in the computing report can easily complete parallel data acquisition tasks to improve report performance. Here, Oracle is used as an example to describe the implementation process.

Report description

The user status table displays detailed data. To export data, you must read all data in the data table at a time. The data volume of a single table is 3.6 million, and the report style is:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/49/E6/wKioL1QeSnrg7xe3AAAwrk2uo5A468.jpg "style =" float: none; "Title =" 2014-09-21_114650.jpg "alt =" wkiol1qesnrg7xe3aaawrk2uo5a468.jpg "/>

Follow these steps:

Write scripts

Use the set calculator to write scripts (parallel. DFX) to implement parallel fetch logic. First, determine the segment field. Here, select the numeric USERID field, and then calculate the userid range in each segment based on the set number of threads and the maximum userid value (specified as a large value constant; finally, read data in segments using SQL based on the specified data range to complete the multi-threaded data acquisition process. The following is the implementation script:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/49/E4/wKiom1QeSl2xqyZLAAF9qfLkTno601.jpg "Title =" 2014-09-21_1120.8.jpg "style =" float: none; "alt =" wkiom1qesl2xqyzlaaf9qflktno601.jpg "/>

A5 fork uses multiple threads to execute code blocks in the grid to achieve parallel fetch;

The number of parallel operations completed by the B6-B9, each thread running results returned A8 lattice;

A10 is the consolidated result set returned by 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.

Set the report template expression:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/49/E6/wKioL1QeSnzBQ05MAABowXJJXBs897.jpg "Title =" 2014-09-21_114707.jpg "style =" float: none; "alt =" wkiol1qesnzbq05maabowxjjxbs897.jpg "/>

A2: Use the select function to obtain the user logon information list based on the dataset ds1;

A2-G2: According to A2 extension, take the user ID, account, online and other information through the value expression respectively.

Parallel Effect

After the report is run, record the dataset computing time, and compare the running results before and after Parallelism:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/49/E4/wKiom1QeSl6xey04AABB0O10Ufo935.jpg "Title =" 2014-09-21_114723.jpg "style =" float: none; "alt =" wkiom1qesl6xey04aabb0o10ufo935.jpg "/>

The preceding figure shows the effect of parallel data acquisition. The parallel mechanism of the Set Computing report can accelerate the data acquisition process, thus improving the overall report performance. The test results vary with the test environment, for the local configuration, see the appendix.

Multi-thread parallel fetch is suitable for idle database resources (for example, the number of connections cannot reach the upper limit). This method makes full use of database resources. If the database task is saturated, this method will further increase the burden on the database and will not increase the speed.

In addition, the selection of segment fields has a great impact on the performance. It is best to select an index field to be inserted incrementally, such as the serial number of the primary key, so that the database can make full use of the index to reduce the traversal range; try to select a field of the numerical type, as a condition, the computing performance is higher and easier to split. In practice, we need to consider these two factors comprehensively. In this example, because there is no index or primary key, the numeric userid is selected. In actual business, you can often find indexed numeric fields inserted incrementally, which improves performance by using parallel data acquisition.

[Appendix] test machine configuration

Test Model: Dell target 3420

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

Ram: 4 GB

HDD: WDC (500g 5400 rpm)

Operating System: win7 (x64) SP1

JDK: 1.6

Database: oracle11g r2

Computing report version: 5.0

Parallel fetch for improving the performance of computing reports in rundry Sets