Database system optimization

The optimization of SQL statements is an excellent SQL statement that converts poorly performing SQL statements to the same purpose.

Artificial Intelligence automatic SQL optimization is the use of artificial intelligence technology, automatically rewrite the SQL statements, so as to find the best performance of the equivalent SQL statements.

Optimization of database performance

The lifecycle of a database system can be divided into three stages: design, development and finished product. Database performance optimization at the design stage is the lowest cost and the most profitable. Database performance optimization at the end of the product phase is the most expensive and the least profitable.

The optimization of a database can often be done by optimizing the network, hardware, operating system, database parameters, and applications. The most common method of optimization is the upgrade of hardware. According to statistics, to optimize the network, hardware, operating system, database parameters to improve performance, all add up to only 40% of the performance of the database system, the remaining 60% system performance improvement from the optimization of the application. Many optimization experts believe that the optimization of the application can be improved by 80% system performance.

Optimization of applications

Optimization of an application can usually be divided into two areas: source code and SQL statement. Because of the changes in the logic of the program, the optimization of source code has a high cost in time and risk, and the improvement of database system performance is limited.

Why to optimize SQL statements

. SQL statement is the only way to operate the database, which plays a decisive role in the performance of the database system.

. The SQL statement consumes 70% to 90% of the database resources.

. SQL statements are independent of program design logic, and optimizing SQL statements does not affect program logic.

. SQL statements have different ways of writing, and the difference in performance is very large.

. SQL statements are easy to learn, but difficult to master.

The traditional approach to optimizing SQL statements is to optimize SQL statements by hand rewriting. The DBA or senior programmer performs a planned analysis of the SQL statement, relies on experience, tries to rewrite the SQL statement, and then compares the results to the performance to try to find a better performance SQL statement. This traditional approach is not able to find all the possible writing of SQL statements, and relies on human experience, very time-consuming.

The development course of SQL optimization technology

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.

The third generation SQL Optimization tool not only analyzes the execution plan of the input SQL statement, but also analyzes the input SQL statement itself, and analyzes and produces the improvement suggestions.

Artificial Intelligence Automatic SQL optimization

Artificial intelligence automatic SQL optimization appeared in the late 90. Currently in the field of commercial databases, LECCO Technology Limited (Ling Gao Research Co., Ltd.) owns the technology and provides automated optimization products using this technology LECCO SQL Expert, which supports Oracle, Sybase, MS sql Server and IBM DB2 database platform. This product provides 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. Its core module, SQL Syntax Optimizer, works as follows: ① input a source SQL statement; ② "AI feedback search engine" to input SQL statements, combined with the detected database structure and index to rewrite, resulting in N equivalent SQL statement output; ③ generated n equivalent SQL statements are then fed into " AI feedback search engine "to rewrite, until the new output or search limit is full; ④ the output of the SQL statement to filter, select a different execution plan of the SQL statement, ⑤ to the resulting SQL statements in batch test to find the best performance of the SQL statement.

LECCO SQL expert Automatic optimization instance

Let's say we extract this SQL statement from the source code (or we can get the SQL statement via the Inner Scanner or monitor):

SELECT COUNT(*)
FROM EMPLOYEE
swheresEXISTS (SELECT 'X'
FROM DEPARTMENT
swheresEMP_DEPT=DPT_ID
AND DPT_NAME LIKE 'AC%')
AND EMP_ID IN (SELECT SAL_EMP_ID
FROM EMP_SAL_HIST B
swheresSAL_SALARY ＞ 70000)