Report server calculation for optimizing report system structure

Hello everyone , I meet you again , I'm the king of the whole stack .

In report items , We often encounter the problem that the database pressure is very large and affects the performance of the whole system . It can be seen from the following structural diagram of the traditional scheme . All data storage and source data calculation are put in the database . When concurrent visits are large , Although the amount of data in each report is small , Or it will cause too much pressure on the database . Become a performance bottleneck . Provided by most database manufacturers jdbc Interface data transmission is relatively slow . In the case of large concurrency , The impact on the performance of the reporting system is also very obvious .

In this case, the local calculation scheme provided by the Runqian centralized calculation report can be considered .

Local computing , It is to move some calculation tasks from the database to the report server Middle over .

Most of the application systems with a certain scale . Database and Application server It is usually deployed on different physical machines . among , The database is at the center , To provide services for various application systems . Suppose that most of the operations are completed by the database , It will cause excessive pressure on the database . The cost and difficulty of database expansion are quite high . And application server Is different , Different applications will have different applications server Hardware , And easy The cluster expansion .

Suppose you can move some operations out of the database . Turn to and Application server Reports deployed together server complete , Will greatly reduce the pressure on the database , And make full use of applications server The computing power of the machine , Improve system performance .

The schematic diagram of the scheme structure of the centralized calculation report is as follows ：

As you can see from the picture above , Runqian centralized calculation report can transfer part of the data from the database to the report application server Local hard disk for . This part of data can be the intermediate result of calculation . It can also be part of the basic data .

The centralized calculation report has built-in centralized calculation engine . Be able to calculate localized data through simple scripts . therefore . It can reduce the pressure of database from both data storage and calculation .

Some data and calculations are transferred from the database to the report application server On , Be able to make full use of applications server Storage and computing power of the cluster . application server Whether it's a stand-alone upgrade （ Vertical expansion ） Or add the number of clusters （ Horizontal scaling ） Than database server The cost of is very low . Reading data on the local hard disk is faster than the database jdbc Very fast , It can solve this bottleneck problem .

The set computing engine can also perform multi-threaded parallel computing , Can give full play to the application server many cpu、 Multi core computing power . therefore . The Runqian centralized calculation report scheme is the preferred scheme to improve the performance of the report application system at low cost .

following , adopt “ A company's customer cumulative sales and last year's sales comparison report ” Making . Let's take a look at how centralized report realizes localized calculation . For example, the following figure ：

The customers in this report 、 Number of orders 、 Sales are calculated directly from the database 2010 year 1 month -10 Month of data .

2009 The number of orders in the whole year 、 Sales are applied from the report server In the file system temp2009sales.b Read... In the file .“ sales / Last year's sales ” It is calculated by the data of this year and last year .

Query in the upper part of the report button It is provided by centralized calculation report “ Parameter template ” function , For details, please refer to the tutorial , No more details here .

First , Read from the database in advance with the integrator 2009 Sales data of various years such as , After the calculation . Export to... With the binary code of the integrator temp2009sales.b In the document , One file a year . After the intermediate data is made , In the database 2009 Years of data can be removed and backed up , No longer occupy the space of the database .

second , Script the totalizer salesProportion.dfx For example, the following ：

Be careful , The parameters of the script are ：argyear（ Year to query ）,argmonth（ The month to query ）.

A1： Connect pre configured data sources demo.

A2： Calculate the number of orders in the year to be queried from the database 、 sales .

A3： Take the data from the data file of the previous year .

A4： take A3 The data in is in accordance with A2 Medium CLIENT Field alignment ,A2 There is A3 Fill in the blank line that is not in .

A5： utilize A2 The next life becomes a new continuation table .

It added A4 The corresponding row of data , example A4(#).C:lastCOUNT Namely A4 The corresponding line of （# yes A2 Current line number of ） Remove from C Field .

A6： Close database connection .

A7： Return the result set to the report .

Third , Define report parameters in the centralized calculation report （argyear、argmonth） And compute datasets ：

Above picture . The parameter name refers to dfx Defined parameter name , Parameter value refers to the value of the report submitted to the centralized calculation engine .

Here is the parameter with the same name that passes the values of the two parameters of the report to the concentrator .

Fourth , Design report , For example, the following figure ：

After input parameter calculation . You can get the desired report .

Publisher ： Full stack programmer stack length , Reprint please indicate the source ：https://javaforall.cn/108394.html Link to the original text ：https://javaforall.cn