Run-dry set report using parallel computing to improve JDBC Fetch performance example

in practical applications, a report with a large amount of data often needs to be taken out of the database at a time when it is presented or exported, and the slow fetch speed of JDBC makes the process extremely inefficient. How to optimize the fetch speed becomes the key to improve the performance of the report. In the run-dry set calculation report, the performance of the JDBC fetch can be improved by parallel computation . This article takes Oracle as an example to illustrate the implementation process.

Report Description

The User Status table shows the detail data, which needs to be read all the data in the data table at once because of the need to export. The amount of data in single table is $ million, and the report style is:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/48/08/wKioL1QFYByC5SEgAAAzmp6F3oc575.jpg "style=" float: none; "title=" report_runqian_parallel_1.jpg "alt=" Wkiol1qfybyc5segaaazmp6f3oc575.jpg "/>

implementation StepsWriting Scripts

The parallel fetch logic is implemented by first using the collector script (parallel.dfx):

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/48/06/wKiom1QFYB3gF3zPAAIjrHKszmE408.jpg "style=" float: none; "title=" report_runqian_parallel_2.jpg "alt=" Wkiom1qfyb3gf3zpaaijrhkszme408.jpg "/>

A1: connecting Oracle data sources;

a2-a3: The minimum and maximum values of the UserID are taken separately for data segmentation;

A4: close the database connection;

A5: Specifies the number of parallel threads, where 4 threads are specified ;

a6-a7: calculate the segment start and end parameter list respectively;

A8: through the use of multi-threaded execution of code blocks in the grid, to achieve parallel fetch number;

b9-b12: completes the parallel fetch, each thread runs the result to return the A8 lattice;

A13: Merges the result set and returns it to the report through A14.

New report

Open the Collection Report Designer, menu bar Select File - new report, click " generate blank Report "and create a new blank report.

Set up Datasets

menu Bar Select Report - DataSet, DataSet type Select " collector ", select the DFX filein the pop-up collector DataSet Settings window and create the dataset Ds1.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/48/08/wKioL1QFYB7w6HW5AADZ-Nm43FA385.jpg "style=" float: none; "title=" report_runqian_parallel_3.jpg "alt=" Wkiol1qfyb7w6hw5aadz-nm43fa385.jpg "/>

To edit a report template and an expression

To set the report style in a new report template:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/48/06/wKiom1QFYB3jh1MKAABmpJwr_zM054.jpg "style=" float: none; "title=" report_runqian_parallel_4.jpg "alt=" Wkiom1qfyb3jh1mkaabmpjwr_zm054.jpg "/>

A2: Use the Select function to take a list of user login information According to the data set DS1;

A2-G2: According to the A2 extension, the user ID, account, and online information are taken by the value expression .

Parallel Post Effects

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/48/06/wKiom1QFYB7xDL-pAABB4u_nZyA593.jpg "style=" float: none; "title=" report_runqian_parallel_5.jpg "alt=" Wkiom1qfyb7xdl-paabb4u_nzya593.jpg "/>

We see the effect of parallel fetching, and in this way make full use of database resources by using multi-threaded parallel fetching for database resources that are relatively idle, such as when the number of connections is not up to the line. If the database task is already saturated, this approach can further aggravate the database burden without increasing the speed.

Test machine configuration

Test Model :Dell Inspiron 3420

CPU :Intel Core i5-3210m @2.50ghz

RAM :4G

HDD : West number WDC (500G 5400 rpm / min )

Operating system :Win7 (X64) SP1

JDK :1.6

Database: oracle11g R2

Collection report version :5.0

Run-dry set report using parallel computing to improve JDBC Fetch performance example