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

Today to talk to you about the report there are 4 SQL data sets, but the report is very slow to check what to do, many people may not understand, in order to let you better understand, the editor summed up the following content, I hope you can get something according to this article.

You need to make sure that the results of the SQL are all loaded, for example, after the PL/SQL Developer check, only the part is loaded.

However, the report run time is certainly slower than the database check SQL, if much slower may be due to the association of multiple data sets in the report. You can check to see if there is an expression like ds2.select (name,id==ds1.cusid) in the report cell, indicating that dataset 2 and dataset 1 are associated through a field.

Almost all report tools use sequential traversal to complete the association of multiple datasets, first take the first record of a dataset to the second dataset to find qualified records, and then the second, the third … The performance will be very low when the amount of data is large Draw a picture and take a look:

Associating the two datasets in the report requires traversing the dataset ds1 records (1 million) and the dataset ds2 for a total of 1 million * 1000 times. Of course it's slow.

To solve this problem, we have to find a way to change the correlation calculation to the stage of preparing data for the report. There are two ways.

First, use SQL to complete the association of the original four data sets.

Write a complex SQL, integrate the SQL of the original four data sets into one sentence, and let the database complete the association calculation (HASH JOIN), which will be much faster. Of course, there are several limitations to this practice:

1. Cannot cross repositories. If the original four datasets come from different databases, you can't do this. Of course, heterogeneous sources are not allowed either.

2. No stored procedures are allowed. The cost of modifying the stored procedure is too high, and the corresponding database permissions are required.

3. SQL is too complex to integrate. Sometimes the dataset SQL of the report is very complex, with a lot of parameters (passed by the report), which is difficult to integrate. In fact, this is the reason why multiple datasets are used in reports. Report tools that support multiple data will bring a lot of convenience, but will affect performance.

Second, directly use report tools with strong computing power.

Some report tools have scripted computing power so that multiple datasets can be associated in advance. This changes the original situation that it is either associated in the database (which is impossible in many cases) or in the report template (the performance is too low), and the performance can often be improved several times to dozens of times.

This article introduces the detailed implementation process: how to improve the performance of multi-source relational reports, the performance of the three examples are improved by 5 times, 26 times and 44 times respectively, the effect is more obvious.

Moreover, with scripting capabilities, the tool also supports cross-library, file, NoSQL and other data sources, as well as the ability to call stored procedures, solving the problems faced by the database.

After reading the above, do you have any further understanding of what to do if there are 4 SQL data sets in the report but the report is checked very slowly? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.