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

This article mainly analyzes the relevant knowledge points of big data cross-report performance optimization example analysis, the content is detailed and easy to understand, the operation details are reasonable, and has a certain reference value. If you are interested, you might as well follow the editor to have a look. Let's go deep into the knowledge of "big data Cross report performance Optimization case Analysis" with the editor.

Software and hardware environment

OS:win7

Cpu:8 kernel

Aggregate report: 1120 installation version

Jvm:1G

Database: oracle11g

Issues that the customer cannot solve:

There is a cross-summary report, in fact, the format is very simple, each column has a statistical dimension. However, there are 1.75 million items of data in the backend business table, and you have to do join with other tables (about 7w items). If it is processed by sql, you can imagine how slow it will be. The key is affected by various conditions, and whether you can find out the data is a problem.

Note: 175w items in ACCORECEIVE table

Currently, it takes 5 minutes to test birt, with the help of various intermediate tables and views. The report competitor cannot produce the table.

Requirements: be able to make the report displayed in web, and it is important to be fast, in addition, the data (currently about 5 years of data) is increased in real time.

Customer report format and current sql:

Report format:

Sql:

Select LOCATIONS.loupan loupan, LOCATIONS.LPORDERNUM, nvl (ACCORECEIVE.RECEIVABLEAMOUNT, 0) yingshou, chargeproct.Description CHARPNAME, chargeproct.ordernum chordernum from ACCORECEIVE,V\ _ LOCATION\ _ LP\ _ LG\ _ DY LOCATIONS Chargeproct where ACCORECEIVE.Org\ _ Id = LOCATIONS.Org\ _ Id and ACCORECEIVE.Sub\ _ Org\ _ Id = LOCATIONS.Sub\ _ Org\ _ Id and ACCORECEIVE.Fk_Locationid = LOCATIONS.Locationid and ACCORECEIVE.Fk_Chargeproctid = chargeproct.chargeproctid (+) and ACCORECEIVE.Wf_Status not in ('invalid') problem Analysis and solution

In the conventional mode, it is almost difficult for big data to produce cross reports, which is affected by the slow efficiency of sql and jvm, so memory overflow is very likely if all the data is taken out at once. In addition, if there is a join in the big data table, even if it can be fetched, the fetch speed must not be guaranteed (the efficiency of sql join is low). All the problems can be reflected in the above sql.

Solution:

1. To avoid memory overflow of one-time fetching, you can use the aggregator cursor cursor to fetch data;-cursor

2. Remove unnecessary fields and join fields. After analysis, it is found that the customer does not actually need the association of org_id and sub_org_id.

3. After taking the number, you can do data processing according to the customer's report. Here, groups can handle a grouped summary;-replace the report expression group.

4. In order to get rid of the problem of low efficiency of sql join, you can put join into the aggregator, where ACCORECEIVE is separated from V_LOCATION_LP_LG_DY table (query, the data is small);-switch connection

Note: the join in two tables sql has been tested in the aggregator, and it takes about 5 minutes.

5. Combined with the customer report format and the database table used, the chargeproct table in the above sql can be put into the report sql to fetch the number, because it only reflects the role of display value, and only dozens of data.

Set calculation script:

Note: the code has instructions for each step.

About "big data cross report performance optimization case analysis" is introduced here, more related content can search the previous article, hope to help you answer questions, please support the website!

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.