Optimizing SQL execution paths to improve report performance

When report performance issues require optimization of data source calculations, execution paths are difficult to determine and intervention is one of the challenges that hinder report optimization. Because the database execution path is opaque to the developer, report optimization needs to specify the execution path, which can be difficult or even impossible for the programmer to intervene. But the general report tool does not have the strong computation ability, most computation still relies on the database to carry on, this causes many report optimization effect not to be ideal.

Unlike the general reporting tools, the run-to-dry account report has built-in set-up engines that are designed for data calculation, and developers can prepare the report data source by writing a collection script. Compared with the database execution SQL path is not controllable, the execution of the set-up script is controllable, the developer can write or change the calculation execution process according to the actual situation, complete the report optimization work.

The collection engine has a rich library of classes for structured data calculations, such as connection operations in the set engine, including common join joins (), Pjoin () for concatenated by ordinal, and Xjoin () for cross-joins, which provide a more efficient way to connect to dimension tables and fact tables, switch (), This provides a variety of choices for developers when optimizing reports. In addition, because the set-up reports support step-writing, developers are free to determine the order of calculation, what to count, and even to reasonably split and integrate a complex computation, suitable for scenarios that need to change the order of calculation to optimize.

The following is an instance procedure and effect for a report data source SQL optimization that users can refer to when using a set report.

The report is a schedule, the data is large, the number of database tables involved in more than dozens of, the association between database tables frequently (including self-connected cases); The report contains multiple inter-grid calculation expressions (ratios and values of sinks).

One of the more complex data set SQL (nearly 400 rows) is as follows:

Select t.*

From (SELECT *

From (select Syb.org_abbreviation as SYB,

Max (xmb.org_abbreviation) as XMB,

--Omit multiple connections, judgments, summary statements

Left Join losrrr LOSR onlosr.requisition_id =

l.requisition_id

where L.table_type = ' 1 '

and l.requisition_state = ' 0100500005000000006 '

and NVL (l.bsflag, 0)! = 1

Group BY L.REQUISITION_ID,

L.note,

--Omit multiple group fields

LOSR.STANDBY_PARAM3,

Losr.standby_param6

) A

Left JOIN Crview ve--View

On ve.requisition_id = a.req_id

Look at this. SQL will find that the first associated table is more, including many self-related cases, nested many subqueries, and finally associated with a view (view complexity is similar to the above SQL). The report query 4 months data display time 6 minutes , far less than the user requirements. Because of the complexity of SQL, the database execution path is difficult to control, so it is difficult to optimize at the level of SQL.

The process of using a set report for optimization is as follows:

1. Write a collection script

First, split the original report dataset SQL. This SQL is slow because of the join between the two subqueries, then the two subquery SQL is written to the collection script execution, and in the collection script using switch to complete the association.

Second, the elimination of the report between the table operation. Move the contents of the inter-grid calculations (ratios and values) from the original report template to the set-up script to complete, so that the report is less of a lattice traversal and can improve performance.

Finally, the result set is returned to the report at once. Once all the data preparation work has been done through the set-up script, the results are returned to the report tool one at a time, and the report is presented directly after the data source is received (no more calculations that affect efficiency of the inter-grid calculation Class).

The complete set of scripts is as follows:

2. Call the above set-up script in the Set calculation report, edit the report expression to finish the report production.

Optimization effect

report presentation to go through two steps: 1 data source calculation (execute DataSet SQL) phase, 2 report calculation and presentation phase, report optimization before and after the various stages and total time comparison is shown in the following table:

Through the above process, the report shows the time from the original 6 minutes 42 seconds, sharp down to the second , less than 1 minutes of time, reached the user's expected target.

Report optimization using a set report is useful for scenarios where the amount of data fetched from a database is not very large (due to the very slow Oracle JDBC speed), which improves report performance quickly by using this controllable process optimization.

Optimizing SQL execution paths to improve report performance