Artificial Intelligence automatic SQL Optimization

The so-called SQL refers to Structured Query Language (Structured Query Language), which is currently the most widely used database language. It is used to deal with databases and obtain user data from databases. However, it is not a simple task to be familiar with SQL statements. Some statements are also troublesome to use. If we optimize SQL statements, it is much easier for users to use them.

To put it simply, the optimization of SQL statements is to convert low-performance SQL statements into SQL statements with excellent performance for the same purpose. Artificial Intelligence automatic SQL optimization is the use of artificial intelligence technology, automatic rewriting of SQL statements, find the best performance equivalent SQL statements.

Artificial Intelligence automatic SQL Optimization

With the development of AI technology and its in-depth application in the field of database optimization, there was a breakthrough in the end of 1990s-artificial intelligence automatic SQL optimization. Currently, Lecco technologylimited (linggao Company) owns this technology in the commercial database field and provides an automatic optimization product using this technology-Lecco SQL expert, it supports Oracle, Sybase, Ms sqlserver and ibmdb2 database platforms. This product provides several special modules for the development and maintenance of database applications: SQL syntax optimizer, PL/SQL integrated development and debugging environment (IDE), scanner, and database monitor. Figure 1 The working principle of "SQL syntax optimizer", one of the core modules of Artificial Intelligence automatic SQL optimization, is roughly as follows:

Input a source SQL statement → "artificial intelligence feedback search engine" to overwrite the input SQL statement in combination with the detected database structure and index, 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 no new output or search quota is available → filter the output SQL statements and select SQL statements with different execution plans (that is, different execution efficiency) → Perform Batch tests on the obtained SQL statements to find the best-performing SQL statements. Figure 2 example of Automatic Optimization of SQL statements before optimizationSource codeExtract this SQL statement

Selectcount (*) FROMEMPLOY-EE whereexists (select 'x' from region = dpt_idand dpt_name like 'ac % ') and emp_idin (select sal_emp_idfrom emp_sal_histb wheresal_salary> 70000)

After pressing the "optimize" button, after more than a dozen seconds, SQL expert completes the optimization process. We can see from the optimization details that, it overwrites and generates 2267 equivalent SQL statements in a dozen seconds, of which 136 have different execution plans.

Next, we can perform a batch run test on the 136 SQL statements generated by automatic rewriting with different execution plans to select the best-performing equivalent SQL statements. Click "batch run", select "optimal run time SQL statement" on the "termination condition" page, and click "OK ". Figure 4 test condition after several minutes of test run, we can find that sql124 has the lowest running time and response time. The running speed is improved by about 22.75 times (the running time of source SQL statements is 2.73 seconds, and that of sql124 is 0.12 seconds,

We put sql124 into the sourceCodeTo end the optimization of an SQL statement. From the above example, we can see that the automatic Rewriting Technology of Lecco SQL expert enables Optimization of SQL statements that took a few hours to complete, which can be completed within minutes. Database administrators and developers can free themselves from the heavy SQL statement optimization work.

Perform edge training

Lecco SQL expert not only can find the best SQL statements, but also provides "learning-based training" 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 detailed context-sensitive Help System of Lecco SQL expert can point out the deep meaning of the execution plan. Figure 6 Comparison of the source statement and sql124 with the above optimization results as an example. To view the differences between the source SQL statement and sql124, we can press the "comparator" button, compare sql124 with the source SQL statement. If you select the "two-way comparison" check box, the "SQL comparator" can represent the two differences in blue. Of course, you can also choose two from the source statement and the rewritten SQL statement for comparison.

From the comparison results, we can see that the first exists is rewritten to in the rewritten sql124; merge null strings on the field dpt_id to induce the database to execute the subquery first (selectdpt_id | 'fromdepart-mentwheredpt_namelike 'ac % ') after the subquery is complete, it then performs nested loop join (nestedloopjoin) with the employee table ). Figure 7 online execution plan help if you find it hard to understand the change in writing, you can also click the "Execution Plan" check box, compare the execution plans of the two SQL statements to see why the two SQL statements differ in performance. If you have any questions about the execution plan, click "SQL Info" and then click where the execution plan cannot be understood, the Lecco SQL expert context-sensitive help system will provide an explanation of the Execution Plan

Features of the optimization module

The Lecco SQL expert optimization module has the following features:

1. Automatically optimize SQL statements;

2. Rewrite SQL statements with excellent performance using the AI knowledge base "feedback-based search engine;

3. Find all equivalent SQL statements and possible execution plans;

4. generate the same result;

5. Advanced SQL syntax analyzer can process the most complex SQL statements;

6. The select, selectin-to, update, insert, and delete statements can be rewritten;

7. Test and run the applicationProgramAnd the database automatically finds the best performance SQL statement;

8. Provides microsecond-level timing;

9. provide "learning-side training ";

10. provides context-sensitive execution plans to help the system and SQL running status;

11. It is not a speculation or suggestion, but an SQL rewrite solution.

Expert-level SQL statements

The emergence of Lecco SQL expert makes SQL optimization easier. It not only finds all possible optimization schemes in a short period of time, but also determines the most effective optimization scheme through actual tests. Compared with the previous database optimization methods, the emergence of Lecco SQL expert can be said to bring the database optimization technology to a new technical height.

 

Http://www.haicode.net/soft/UploadFile/2007-11/Registar_LECCO_ SQL _Expert_For_ SQL _Server_v25.rar

Download instructions

Lecco SQL expert for SQL Server, a useful SQL statement optimization tool
The compressed package contains registration machine and Chinese files

Software cracking registration and localization instructions
Method 1:
1. Install files in the demo directory
2. overwrite all the files in the crack directory
3rd, use cr-sql25.exe to generate registration code registration

Method 2:
The first installation requires that the serial number be available in the trial version:
898724 to 355633-859461-053221
After the installation and then use the tool with the cr-sql25 to generate a registration number to register it can be used completely

Usage of Chinese files:
After Lecco SQL expert for SQL Server is installed, copy the file to the installation directory.

 

 

Program development, especially web development, has high performance requirements. Online list information needs to be quickly used to reduce server resources, and data can be read quickly in a popular network, in terms of program optimization, we only write the code quality. We will not discuss code optimization here. This article focuses on optimizing database query statements.

Programmers or DBAs may rewrite low-performance SQL statements through years of experience, but it is difficult for me to write high-performance SQL statements ..., Don't be afraid. Now I will introduce a tool, Lecco SQL expert. The download and use of this tool will help you find a lot in Baidu. No download link is provided here.

Now, let's start our topic today:

1. Open the software. After logging on, the following page appears.

 

Find the SQL statement that I want to test, as follows:

Select DBO. Contract application form. ID,

DBO. Contract application form. applyuser,

DBO. usertable. username,

DBO. Contract application form. applytype,

DBO. Contract application form. Name,

DBO. Contract application form. object,

DBO. Contract application form. intention,

DBO. Contract application form. Delivery,

DBO. Contract application form. Howmuch,

DBO. Contract application form. Payment,

DBO. Contract application form. Transit,

DBO. Contract application form. Others,

DBO. Contract application form. memo,

DBO. Contract application form. status,

DBO. contract status table. Title As statustitle,

DBO. Contract application form. attime,

DBO. usertable. Phone,

DBO. Contract application form. Serial

From DBO. Contract application form,

DBO. usertable,

DBO. contract status table

Where DBO. Contract application form. Status = @ nstatus

And DBO. Contract application form. applytype = @ ctype

And DBO. Contract application form. workerid = @ nofficer

And DBO. Contract application form. Status = DBO. contract status table. ID

And DBO. Contract application form. applyuser = DBO. usertable. ID

 

2. Copy it to the SQL editor of Lecco SQL expert ,:

 

3. Click the optimization button in the toolbar and enter the parameters (the SQL statement uses the variable @ nstatus, @ ctype, @ nofficer ),:

 

 

4. Enter the corresponding variable value and click OK to query the optimization statement ::

 

 

Find 5 optional execution plans and run these five SQL statements in batches. The best SQL statement is to optimize the SQL statements based on the minimum running time.

5. batch run and display the five running times

 

 

 

The result shows that sql4 is optimal and its statement is:

Select DBO. Contract application form. ID,

DBO. Contract application form. applyuser,

DBO. usertable. username,

DBO. Contract application form. applytype,

DBO. Contract application form. Name,

DBO. Contract application form. object,

DBO. Contract application form. intention,

DBO. Contract application form. Delivery,

DBO. Contract application form. Howmuch,

DBO. Contract application form. Payment,

DBO. Contract application form. Transit,

DBO. Contract application form. Others,

DBO. Contract application form. memo,

DBO. Contract application form. status,

DBO. contract status table. Title As statustitle,

DBO. Contract application form. attime,

DBO. usertable. Phone,

DBO. Contract application form. Serial

From DBO. Contract application form

Inner join DBO. contract status table

On DBO. Contract application form. Status = DBO. contract status table. ID

Inner join DBO. usertable

On DBO. Contract application form. applyuser = DBO. usertable. ID

Where DBO. Contract application form. Status =@ Nstatus

And DBO. Contract application form. applytype =@ Ctype

And DBO. Contract application form. workerid =@ Nofficer

Option ( force order )