Optimize SQL Execution path to improve report performance and SQL Execution path report

Source: Internet
Author: User

Optimize SQL Execution path to improve report performance and SQL Execution path report

When performance problems occur in reports and data source computing needs to be optimized, it is difficult to determine the execution path. Therefore, intervention is one of the challenges hindering report optimization. Since the execution path of the database is not transparent to developers, it is difficult or even impossible for programmers to intervene when the execution path needs to be specified for report optimization. Generally, report tools do not have strong computing capabilities. Most of the calculations still rely on databases, which leads to unsatisfactory report optimization results.

Unlike the general report tools, the computing engine for data computing is built into the rundry set computing report. developers can compile a set computing script to prepare the report data source. Compared with the uncontrollable SQL Execution path of the database, the execution process of the computing script is controllable. developers can write or change the computing execution process based on the actual situation to complete report optimization.

The computing engine has a wide range of built-in class libraries for structured data computing. For example, join operations include join () for common connections and pjoin () for serial numbers () cross join () provides a more efficient connection mode for connecting dimension tables and fact tables (), this provides multiple options for developers to optimize reports. In addition, as the computing report supports step-by-step writing, developers can freely determine the computing sequence, calculate what is first, and even reasonably split and integrate a complex computing, it is suitable for scenarios where the computing sequence needs to be changed for optimization.

The following is an example of SQL optimization for the report data source. You can refer to it when using the set computing report.

This report is a detailed table with a large amount of data. It involves dozens of database tables and is frequently associated with database tables (including self-join situations ); the report contains multiple calculation expressions (ratio and total value) between cells ).

The complex dataset SQL (nearly 400 rows) is as follows:

Select t .*

From (select *

From (select syb.org _ abbreviation as syb,

Max (xmb.org _ abbreviation) as xmb,

-- Multiple join, judgment, and summary statements are omitted.

Left join losrrr losr onlosr. requisition_id =

L. requisition_id

Where l. table_type = '1'

And l. requisition_state = '20140901'

And nvl (l. bsflag, 0 )! = 1

Group by l. requisition_id,

L. note,

-- Omitting multiple grouping Fields

Losr. standby_param3,

Losr. standby_param6

)

Left join crview ve -- View

ON ve. requisition_id = a. req_id

By taking a closer look at this SQL statement, we will find that there are many associated tables, including many self-associated tables, and many subqueries are nested; finally, it is associated with a view (the view complexity is similar to the preceding SQL ). The four-month data display time for this report to be queried is 6 minutes 42 seconds, far from meeting user requirements. Because SQL is complex and the execution path of the database is difficult to control, it is difficult to optimize it at the SQL level.

 

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

1. Compile the computing script

First, split the SQL statement of the original report dataset. This SQL statement is slow because of the join operation between two subqueries. The two subqueries are written to the Set Computing script for execution respectively, and the switch is used in the Set Computing script to complete the association.


Second, the inter-table calculation is eliminated. Move all the content of Inter-Grid Calculation (ratio and total value) in the original report template to the Set Computing script. This reduces the process of grid traversal in the report and improves the performance.

Finally, return the result set to the report once. After all data preparation is completed through the computing script, the results are returned to the report tool at one time, the report is directly displayed after receiving the data source (other computing tasks, such as inter-grid computing, affect the efficiency ).

The Complete Set Computing script is as follows:

2. Call the preceding set computing script in the Set Computing report to edit the report expression to complete report creation.

 

Optimization results

Two steps for report Presentation: 1. Data Source computing (executing dataset SQL) and 2. Report computing and presentation. The following table compares the stages and total time before and after report optimization:


Through the above process, the report display time dropped from 6 minutes 42 seconds to 57 seconds, less than one minute, and reached the user's expected goal.

 

Using a set computing report for report optimization is suitable for scenarios where the amount of data retrieved from the database is not very large (due to the slow speed of Oracle JDBC). Using this controllable process optimization method, the report performance is rapidly improved.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.