SQL statement Performance Optimization--lecco SQL Expert

The optimization of SQL statements is to convert poorly performing SQL statements into the same performance SQL statements with the same purpose.

AI automatic SQL optimization uses AI technology to automatically rewrite SQL statements to find the best-performing equivalent SQL statements.

Optimization of database performance

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

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

Optimization of the application

The optimization of an application can usually be divided into two areas: source code and SQL statement. Because of the change of program logic, the optimization of source code is expensive in time cost and risk, and the performance of database system is improved.

Why to optimize SQL statements

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

. SQL statements consume 70% to 90% of the database resources.

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

. SQL statements are written differently and differ greatly in performance.

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

The traditional approach to optimizing SQL statements is to optimize SQL statements by manual rewriting. By performing a planned analysis of the SQL statements, the DBA or senior programmer relies on experience, attempts to rewrite the SQL statements, and then compares the results to performance to try to find better-performing SQL statements. This traditional approach does not identify all possible ways of writing SQL statements, and relies on human experience, which is time-consuming.

The development process of SQL optimization technology

The first generation of SQL optimization tools is the Execution Plan analysis tool. This type of tool extracts execution plans from the database for input SQL statements, and explains the meaning of the keywords in the execution plan.

The second-generation SQL Optimization tool can only provide recommendations for increasing the index, and it generates recommendations for whether to increase the index by analyzing the execution plan of the input SQL statements.

The third-generation SQL Optimization Tool not only analyzes the execution plan of the input SQL statements, but also parses the input SQL statements themselves and makes suggestions for improvement in the analysis.

AI Automatic SQL optimization

Figure 1 AI 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.) has this technology and provides automated optimization products using this technology LECCO SQL Expert, which supports Oracle, Sybase, MS sql Server and IBM DB2 database platforms. This product provides modules for the development and maintenance phases of the database application: SQL syntax optimizer, PL/SQL Integrated Development Debugging Environment (IDE), scanner, database Monitor, and so on. The core module of the SQL Syntax optimizer works as follows: ① input a source SQL statement, ② "AI feedback search engine" on the input SQL statement, combined with the detected database structure and index to rewrite, produce n equivalent SQL statement output; ③ generated n equivalent SQL statements are then fed " AI feedback search engine "rewrite until the new output cannot be generated or the search quota is full; ④ the SQL statements of the output and selects the SQL statements with different execution plans; ⑤ the resulting SQL statements in batch testing to find the best performance SQL statements.

LECCO SQL Expert auto-optimization instance

Let's say we extract the SQL statement from the source code (or we can get the SQL statement through the inside of the 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)

After pressing the "Optimize" button, after 10 seconds, SQL expert completes the optimization process, and in these 10 seconds the rewrite produces 2,267 equivalent SQL statements, where 136 SQL statements have different execution plans.

Next, we can run a batch test on the 136 SQL statements generated by the auto-rewrite to select the best-performing equivalent SQL statement. Press the Batch Run button, select best run time SQL statement on the termination criteria page, and press OK.

After a few minutes of testing, we can see that the SQL124 has the shortest run time and response time. The speed is about 22.75 times times Higher (the source SQL statement runs for 2.73 seconds and the SQL124 run time is 0.12 seconds). Now we can put the SQL124 into the source code and end the optimization of a SQL statement.

"Learning by doing" improves the level of SQL development

LECCO SQL Expert is not only able to find the best SQL statement, it provides "side-by-side learning training" to teach developers and database administrators how to write the best performance SQL statements. LECCO SQL Expert's SQL comparator can indicate the difference between source SQL and the SQL to be selected.

For example, in order to see how the source SQL statements and SQL124 differ in their wording, we can press the comparator button to compare the SQL124 and the source SQL statements. The SQL comparer represents the SQL124 of a blue color relative to the source SQL statement. If you select the two-way comparison check box, the SQL comparer can represent the difference between two SQL statements in blue. Of course, we can also compare the source statement and the rewritten SQL statement with any of the two bars selected.

As can be seen from the comparison, the rewritten SQL124 has rewritten the first exists to in; an empty string is merged on the field dpt_id to induce the database to execute the query in the subquery first.

(SELECT dpt_id| | "

From DEPARTMENT

WHERE dpt_name like ' ac% ')

After the subquery is complete, the nested loop joins with the employee table (Nested loop join).

If you feel that the changes to the writing are difficult to understand, you can also point to the "Execution plan" check box and compare the execution plan of the two SQL statements to see the differences. In the process of viewing the execution plan, if there is something that you do not understand, you can click on the "SQL Information button" and tap the execution plan to see where it is not clear, LECCO SQL Expert's context-sensitive Help system will provide an explanation of where the execution plan is.

In the SQL comparer, when you select the Statistics check box, you can get a detailed comparison of the two SQL statement runtime statistics, which is useful for learning different SQL notation for the consumption of database resources.

Features of the LECCO SQL expert optimization module

The features of the LECCO SQL Expert Optimization module are as follows: automatic optimization of SQL statements, the use of an exclusive AI Knowledge base "Feedback search engine" to rewrite the performance of the SQL statements; Find all the equivalent SQL statements and possible execution plans; guaranteed to produce the same results , advanced SQL parser can handle the most complex SQL statements, can rewrite select, select INTO, UPDATE, insert and DELETE statements, test run, automatically find the best performance SQL statements for the application and database, provide microsecond-level timing, Ability to optimize Web applications and short-running SQL statements in online transactions with a large number of users, provide developers with "edge-to-side learning training", quickly improve the developer's SQL programming skills, provide context-sensitive execution plan help system and SQL health status Help; It's a unique SQL rewrite solution.

Write an expert-level SQL statement

The advent of LECCO SQL expert makes the optimization of SQL extremely simple, as long as the SQL statement can be written, it can help users find the best performance. LECCO SQL Expert not only can find all possible optimization schemes in a short time, but also can determine the most effective optimization scheme through actual testing. Compared with the previous methods of database optimization, LECCO SQL Expert brings the database optimization technology to a brand-new technology height, and the database optimization method that relies on human experience, time-consuming and people's thinking is replaced by efficient, time-saving and accurate automatic optimization software. With the help of the built-in "Lecco Little Helper", even novice developers of SQL can quickly and easily write expert-level SQL statements.

Article source: SQL statement Performance Optimization--lecco SQL Expert

SQL statement Performance Optimization--lecco SQL Expert