Report fetching number of report performance optimization scenarios

1. Principle of taking numbers

The designer spells out the final SQL, passes the SQL statement to the database, executes the database, and returns the data to the designer.

Because the calculation process first takes the data from the database through the SQL statement, we can improve the performance of the report by controlling the size of the data and pre-preprocessing the data. Here are some ways to optimize.

2. Optimizing SQL

The data set of the Finereport report uses a table model, which means that a relational table is obtained from the database through a simple query or a variety of combined association queries, which is very mature based on the long-time optimization of various database vendors (such as indexing). Datasets generally need to be finereport by the complex processing of the report model to produce the final sample. As a result, the less data is fetched from database SQL queries, the less complex processing and computation the Finereport report model needs to do, the less time and memory it takes, and the more performance it can improve.

2.1 SQL statements to take specific fields

We generally use a select * from this form to remove all the fields from a database table, some of which are not needed in the report, for example, only three fields are needed in the report, but the actual table in the database has 10 fields, some beginners habitually use SELECT * from Table1, this is equivalent to the 10 field data are taken to the report server side, increase the memory consumption of the report server side and slow down the operation speed, so the SQL statement as far as possible not to use the "*" number, but write specific fields, can reduce the memory consumption of the report server side, speed up the calculation of the report.

2.2 Direct grouping in SQL in lieu of grouping in a report

Some summary types of reports, such as a table to make an order total, may take a large number of data records from the order schedule, and then summarize the data, that is, group aggregation operations, the report calculation process we can be in the SQL in advance of a group aggregation, can greatly reduce the number of records to the report server fetched, Speed up the number of fetch and report operations.

SQL statement: SELECT cost price, category ID from product

Select the two fields from the database and then summarize the cost price based on the category ID, at which point the database has 77 data returned to the report processing. As follows:

Optimized SQL statement: SELECT sum (cost price), category ID from product Group by Category ID

With SQL optimization, there are only 8 data left for the report to process. As follows:

Optimization Analysis:

The first approach, not only to the report server on the number of records, the number of slow, and the report model needs to Group table data column operations, increase the report run time;

The second approach, although the database to be grouped operations, but the database has an index, the operation speed, and take to the report server side of the number of records greatly reduced, take the number of speed greatly accelerated, so in the report model grouping operation as long as the few records, the report operation Speed greatly accelerated.

Experimental results and analysis show that the second approach is much better than the first. Therefore, the grouping should be done in SQL as much as possible.

2.3 Direct sorting in SQL instead of sorting in a report

There are many times when you need to sort the data in the calculation of the report, although the sort operations can be done on the report side, but we recommend sorting the data ahead of time in SQL, because the indexing function in the database, often written in C + + languages (often better than Java), makes sorting operations fast.

2.4 Direct filtering in SQL instead of filtering in reports

There are many times in the report calculation that you do not need to operate on all the records in a table, but only those that partially satisfy the criteria, although you can filter the data in the Report Designer, but we recommend that you filter the data ahead of time in SQL so that the data returned by the database is reduced, which speeds up the speed of the fetch. It also speeds up the calculation of the report.

3. Using views, stored procedures

A view is a virtual table defined by a query that consists of a SELECT statement that consists of data from the actual tables of one or more databases, and from the outside of the database system, the view is like a table.

Stored procedures through flow control and SQL statements, data can be powerful operations and processing, for business complex applications, often need to process the original data through the stored procedure and then for the report to use. In addition, before the stored procedure is run, the database is parsed and optimized for syntax and syntax, and this compiled stored procedure greatly improves the performance of the SQL statement. On the report side, you only need to write short call statements to get the results, reducing network traffic.

So table-to-table connection, complex SQL as far as possible in the database using views or stored procedures directly, so that the complex SQL statements directly on the database server side (the database itself will parse SQL statements and optimize), On the Report Designer side, you do not need to write a large segment of the SQL statement, but directly invoke the view or stored procedures, on the one hand, reduce network traffic, reduce the pressure on the database, on the other hand, speed up the calculation of the report.

Report fetching number of report performance optimization scenarios