Shulou(Shulou.com)06/03 Report--

JAVA applications have to fetch numbers from the database through JDBC, and sometimes we find that the burden of the database is not heavy and SQL is simple, but the fetch speed is still very slow. Careful testing shows that the performance bottleneck is mainly on JDBC. For example, the JDBC performance of MySQL is very poor, and Oracle is not good. However, JDBC is a package provided by the database vendor, and we have no way to improve performance externally.

The conceivable way is to use multi-CPU means to adopt parallel programs to speed up, but the parallel programs of Java are very difficult to write, so it is necessary to consider troublesome transactions such as resource sharing conflicts.

The following describes the use of the parallel technology of the aggregator to improve the database JDBC fetch performance, which can avoid the complexity of JAVA hard coding and facilitate the merging of multithreaded result sets. Applicable to:

Query report with large source data

Multi-data set report

ETL data extraction

Parallel configuration of aggregator

The parallel properties of the aggregator need to be configured before parallel fetching through the aggregator. In IDE, set the maximum number of parallelism supported by IDE through the menu "tools-options". Generally speaking, it is recommended that the maximum number of parallelism should not exceed the number of CPU cores.

The server of the aggregator needs to modify the raqsoftConfig.xml configuration:

Single table parallel fetching

Sometimes we query a table that has a large amount of data and takes a long time, so we can improve the performance by using the aggregator to fetch data in parallel for a single table. The so-called single table here refers to reading a copy of (single table) data in parallel through conditions.

Full memory

Assume that memory can hold all the data to be read, and then proceed to the next step after parallel data fetch (the calculation speed of full memory is the fastest).

Give an example

An order (Orders) has fields such as order ID, order date, order amount, and so on, where order ID is an incremental integer logical primary key.

[computing target] read the number of orders in a certain period of time in parallel

Parallel data fetching for a single table (single SQL) requires splitting the source data into multiple segments through parameters and establishing multiple database connections for parallel queries. It is often necessary to split the data as evenly as possible to avoid task waiting caused by uneven query time, and segmentation parameters are established on the index field as far as possible to ensure segmentation efficiency.

Aggregator implementation

Aggregator parameters

Create script parameters according to the query time period, query start and end date

Aggregator implementation

Segmentation strategy (1) Segmentation based on index field

Data segmentation is needed before parallel data fetching based on single table (single SQL) to ensure the average data of each segment as far as possible. The segmentation parameters are based on the indexed field (such as the order number) as much as possible. The reason for using index fields for segmentation is that using indexes does not actually traverse the entire table, but directly locates, with obvious advantages when the amount of data is large.

Aggregator script

Write a parallel fetch script, which is segmented according to the indexed order number:

ABC1=connect ("db")

2=A1.query ("select min (order ID) minimum ID,max (order ID) maximum ID from order where order date > =? and order date =? and order ID=? and order date = date ('" / begin/ "') and order date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.