Automatic optimization of SQL statements

Optimization | statement

Writing executable SQL statements in a database application system can be implemented in many ways, but it is difficult to determine which is the best solution. In order to solve this problem, it is necessary to optimize SQL implementation. Simply put, the optimization of SQL statements is the conversion of poorly performing SQL statements to better performance SQL statements that achieve the same purpose.

Why SQL statements are optimized

The lifecycle of a database system can be divided into three stages: design, development and finished product. Optimization at the design stage is the lowest cost and the most profitable. Optimization at the end of the product stage is the highest cost and the least benefit. If a database system is likened to a building, after the building of the correction is often costly and small results (may not even be able to correct), and in the building design, production stage control of the quality of each tile can achieve a small cost and high efficiency.

In order to achieve maximum benefit, people often need to optimize the database. The optimization of a database can often be done by optimizing the network, hardware, operating system, database parameters, and applications. According to statistics, the performance improvements to network, hardware, operating system, and database parameters are all added up to about 40% of the performance of the database application system, and the remaining 60% of systems can improve all of the optimization from the application. Many tuning experts even believe that optimization of applications can achieve a 80% improvement in system performance. Therefore, it is certain that the optimization of the database system by optimizing the application can achieve greater benefits.

Optimization of an application can usually be divided into two areas: source code optimization and SQL statement optimization. Because of the changes in the logic of the program, source code optimization costs a lot of time and risk (especially for systems in use). On the other hand, the optimization of source code has a limited effect on the performance of the database system, because the operation of the database by the application will ultimately be expressed as the operation of the SQL statement on the database.

There are some immediate reasons for optimizing SQL statements:

1. SQL statement is the only way to operate the database (data), the execution of the application finally boils down to the execution of the SQL statement, and the efficiency of the SQL statement plays a decisive role in the performance of the database system.

2. SQL statements consume 70%~90% database resources.

3. SQL statements are independent of program design logic, optimization of SQL statements does not affect program logic, compared to the optimization of program source code, the cost of SQL statement optimization in the time cost and risk is very low.

4. SQL statements can be written in different ways, and the differences in performance can be significant.

5. SQL statement easy to learn, difficult to master. The performance of SQL statements is often related to the database structure and record quantity of the actual running system, and there is no universally applicable law to improve the performance.

the traditional optimization method

SQL program personnel traditionally use manual rewriting to optimize SQL statements. This relies primarily on the DBA or senior programmer to analyze the SQL statement execution plan, relying on experience, trying to rewrite the SQL statement, and then comparing the results to the performance to try to find a better performance SQL statement. This approach has the following deficiencies:

1. Unable to find all possible writing of SQL statements. It is possible to spend a lot of time and not find a better performance SQL statement. Even if you find a better performance SQL statement, there is no way to know if there is a good performance.

2. Very dependent on human experience, the amount of experience often determines the performance of optimized SQL statements.

3. Very time-consuming. Rewrite--> checksum correctness--> compare performance, this cycle process requires a lot of time.

According to the traditional SQL optimization tools, people generally divide the optimization tools into the following three-generation products:

The first generation of SQL optimization tools is the Execution Plan analysis tool. This type of tool extracts the execution plan from the database for the input SQL statement and interprets the meaning of the keyword in the execution plan.

The second generation of SQL optimization tools can only provide suggestions for adding indexes, and it generates suggestions for whether to add indexes by analyzing the execution plans of the imported SQL statements. There is a fatal flaw in such tools--analyzing only one SQL statement to conclude that an index is added, regardless of the effect of an increased index on the performance of the overall database system.

The third generation tool is the use of artificial intelligence to achieve automatic SQL optimization.

Artificial Intelligence automatic SQL optimization

With the development of artificial intelligence and the depth of application in the field of database optimization, the technology of optimization in the the late 1990s has made a breakthrough progress, and the artificial intelligence automatic SQL optimization has appeared. The essence of Artificial intelligence automatic SQL optimization is to use artificial intelligence technology to automatically rewrite SQL statements to find the equivalent SQL statements with the best performance. LECCO SQL Expert employs this artificial intelligence technology, and its SQL expert supports Oracle, Sybase, MS SQL Server, and IBM DB2 database platforms. The salient feature is the automatic optimization of SQL statements. In addition, we can use the artificial intelligence Knowledge Base "feedback search engine" to rewrite the SQL statements, and find all the equivalent SQL statements and possible execution plans, through the test run for the application and database automatically find the best performance of the SQL statements, providing microsecond timing; The ability to optimize Web applications and SQL statements that have a short running time in online transactions with a large number of users; By comparing the differences between source SQL and the SQL to be selected, the developer is provided with "side-by-side learning training" to quickly improve the developer's SQL programming skills and so on.

The tool provides several special modules for the development and maintenance phases of database applications: SQL Syntax optimizer, PL/SQL integrated development Debugging environment (IDE), scanner, database Monitor, and so on. One of its core modules, "SQL syntax Optimizer", works roughly as follows: input a source SQL statement, "Artificial Intelligence feedback search engine" on the input of SQL statements combined with the detected database structure and index rewrite, resulting in N equivalent SQL statement output, resulting in N equivalent SQL statements sent to " AI feedback search engine "to rewrite, until the new output or search limit is full, next to the output of the SQL statement filter, choose a different execution plan of the SQL statement (different execution plan means different execution efficiency), and finally, the resulting SQL statements for batch testing, Find the best performance SQL statement (see figure below).

Figure Artificial Intelligence automatic SQL optimization diagram

LECCO SQL expert not only finds the best SQL statement, it provides "side-by-side learning training" to teach developers and database administrators how to write the best performance of SQL statements. LECCO SQL Expert SQL statement automatic optimization makes SQL optimization very simple, as long as you can write SQL statements, it can help developers find the best performance.

Summary

SQL statement is a very critical part of database application, and its performance has a direct impact on the running efficiency of the application. Because of this, people put a lot of effort into the optimization of SQL statements, there are many SQL statement optimization tools. With the growing sophistication of artificial intelligence and other related technologies, there will surely be more and better tools to come, which will help developers more.