SQL statement Performance Optimization-Lecco SQL expert

The optimization of SQL statements is to reduce the performance of SQL statementsConversionSQL statements with the same performance as the target.

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

Database performance optimization

OneDataThe lifecycle of the database system can be divided into three stages: design, development and product development.DataDatabase performance optimization has the lowest cost and the greatest benefit. In the final stage, database performance optimization has the highest cost and the lowest benefit.

Database optimization usually involves network, hardware, operating system, database parameters, and applications.Program. The most common optimization method is hardware upgrade. According to statistics, the performance improvement obtained by optimizing network, hardware, operating system, and database parameters only accounts for about 40% of the database system performance improvement, the remaining 60% system performance improvement comes from application optimization. Many optimization experts believe that application optimization can improve system performance by 80%.

Application Optimization

Application optimization can be divided into two aspects:Source codeAnd SQL statements. Due to changes to the program logicCodeThe optimization cost is very high in terms of time cost and risk, but the improvement of the database system performance is limited.

Why optimize SQL statements?

. SQL statements are the only way to operate databases and play a decisive role in the performance of database systems.

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

. SQL statements are independent of the program design logic. Optimization of SQL statements does not affect the program logic.

. SQL statements are written in different ways and vary greatly in performance.

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

The traditional method to optimize SQL statements is to optimize SQL statements by manual rewriting. By analyzing the execution plan of SQL statements, DBA or senior programmers attempt to rewrite SQL statements based on experience, and then compare the results with the performance to find SQL statements with better performance. This traditional method cannot identify all possible SQL statements and relies on human experience, which is time-consuming.

Development History of SQL Optimization Technology

The first generation of SQL optimization tools is the execution plan analysis tool. These tools extract execution plans from the database for input SQL statements and explain the meaning of keywords in the execution plan.

The second generation of SQL optimization tool can only provide recommendations for adding indexes. It analyzes the execution plan of input SQL statements to generate recommendations for increasing indexes.

The third generation SQL optimization tool not only analyzes the execution plan of input SQL statements, but also performs syntax analysis on the input SQL statements themselves. After analysis, it provides suggestions for improving the writing method.

Artificial Intelligence automatic SQL Optimization

Figure 1 artificial intelligence automatic SQL Optimization

Artificial Intelligence automatic SQL optimization appeared at the end of 1990s. Currently, Lecco Technology Limited (linggao scientific research Co., Ltd.) owns this technology in the commercial database field and provides the Lecco SQL expert, an automatic optimization product that uses this technology, it supports Oracle, Sybase, ms SQL Server, and IBM DB2 database platforms. This product provides the following modules for database application development and maintenance: SQL syntax optimizer, PL/SQL integrated development and debugging environment (IDE), scanner, and database monitor. The working principle of the SQL syntax optimizer in its core module is: ① input a source SQL statement; ② "AI feedback-based search engine" on the input SQL statement, rewrite with the detected database structure and index to generate n equivalent SQL statement outputs; ③ generate n equivalent SQL statements and then send them to the "Ai feedback-type search engine" for rewriting, until a new output or full search quota cannot be generated. ④ filter the output SQL statements and select SQL statements with different execution plans. ⑤ Perform Batch tests on the obtained SQL statements, find the best-performing SQL statement.

Automatically optimized instances of Lecco SQL expert

Suppose we extract this SQL statement from the source code (you can also obtain the SQL statement through the internal 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 & gt; 70000)

After pressing the "optimize" button, after 10 seconds, SQL expert completes the optimization process and overwrites 2267 equivalent SQL statements in these 10 seconds, among them, 136 SQL statements have different execution plans.

Next, we can perform a batch test on the 136 SQL statements generated by automatic rewriting to select the equivalent SQL statement with the best performance. Click "batch run", select "optimal run time SQL statement" on the "termination condition" page, and click "OK ".

After several minutes of testing, we can find that sql124 has the lowest running time and response time. The running speed is improved by about 22.75 times (the source SQL statement running time is 2.73 seconds, and the sql124 running time is 0.12 seconds ). Now we can put sql124 in the source code to end the optimization of an SQL statement.

"Learning-based training" to improve SQL Development

Lecco SQL expert not only can find the best SQL statements, but also provides "training while learning" to teach developers and database administrators how to write the best-performing SQL statements. The "SQL comparator" of Lecco SQL expert can indicate the differences between the source SQL and the SQL to be selected.

The preceding optimization result is used as an example. To view the differences between the source SQL statement and the sql124 statement, we can press the "comparator" button to compare the sql124 and source SQL statements. The "SQL comparator" shows the differences between sql124 and the source SQL statement in blue. If you select the "two-way comparison" check box, "SQL comparator" can represent the differences between the two SQL statements in blue. Of course, we can also compare the two from the source statement and the rewritten SQL statement.

From the comparison results, we can see that the first exists is rewritten to in sql124, And the Null String is merged on the field dpt_id to induce the database to execute

(Select dpt_id |''

From Department

Where dpt_name like 'ac % ')

After the subquery is complete, it then performs nested loop join (nested loop join) with the employee table ).

If you find it hard to understand the change in writing, you can also click the "Execution Plan" check box to compare the different execution plans of the two SQL statements to understand the differences. When viewing the execution plan, if there is anything you don't understand, click "SQL Info" and click what you don't understand, the Lecco SQL expert context-sensitive help system will provide explanations for the execution plan.

In "SQL comparator", select the "Statistics" check box to obtain detailed statistical information comparison during the running of the two SQL statements.LearningDifferent SQL statements are helpful for database resource consumption.

Features of the Lecco SQL expert optimization module

The Lecco SQL expert optimization module has the following features: Automatic Optimization of SQL statements; Use the exclusive AI knowledge base "feedback-based search engine" to rewrite SQL statements with excellent performance; find all equivalent SQL statements and possible execution plans; ensure the same results are generated; advanced SQL syntax analyzer can process the most complex SQL statements; you can override select, select into, update, insert, and delete statements. By testing and running, you can automatically find the best-performing SQL statements for applications and databases. It provides time in microseconds, it can optimize web applications and SQL statements that run for a short time in online transaction processing for a large number of users. It provides developers with "learning-based training ", quickly improve developers' SQL programming skills; provide context-sensitive execution plans to help the system and SQL running status; rather than making guesses or suggestions, it is a unique SQL rewriting solution.

Write expert-level SQL statements

The emergence of Lecco SQL expert makes SQL optimization extremely simple. As long as you can write SQL statements, it can help you find the best performance writing method. Lecco SQL expert can not only find all possible optimization solutions in a short period of time, but also identify the most effective optimization solutions through actual tests. Compared with the previous database optimization methods, Lecco SQL expert brings the database optimization technology to a new technical level, database optimization methods that depend on people's experience, consume a lot of time, and are bound by people's thinking have been efficiently, timely, and accurately automatically optimized.SoftwareReplaced. With the help of the built-in "Lecco assistant", even new SQL developers can quickly and easily write expert SQL statements.