SQL statement optimization tool Lecco SQL expert

Software Description:
Better and faster artificial intelligence automatic SQL optimization ---------- http://www.sina.com.cn 2001/12/12 Chinese computer education packets/SQL enthusiasts so-called SQL, is structured query language (Structured Query Language ), it is currently the most widely used database language. It is used to deal with databases and obtain the data required by users 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 artificial intelligence technology and in-depth application in the database Optimization field, 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.

Download Tool

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,EspeciallyWebDevelopment,High Performance Requirements,Online list information must be used up quickly and reduce server resources,Data can be read quickly in a popular network.,Program Optimization,Only write code Quality,Code optimization is not discussed here,This article focuses on optimizing database query statements..

Programming orDBAWith years of experience, it is possible to rewriteSQLStatement,But for cainiao like me,Write high-performanceSQLSome difficulties.Ah...,Don't worry,I will introduce a tool now, Lecco SQL expert,Download and use this tool to help youBaiduLower,There's a bunch.The download link is not provided here..

Okay.,Start today's topic:

1.Open Software,After Login,The following page is displayed:.

 

FindSQLStatement,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.Change itCopyToLecco SQL expertOfSQLEditor,:

 

3.Click the optimization button in the toolbar.,Input parameters(SQLThe statement uses a variable.@ Nstatus, @ ctype, @ nofficer ),:

 

4.Enter the corresponding variable value,Click OK,Optimize statement Query::

 

 

Find5Optional execution plans.,Batch run this5ItemsSQL,The minimum running time,It is the best OptimizationSQL

5.Batch run,And display5Running time (s)

 

 

 

Result Display: Sql4Is optimal,The 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)

 

Log on to the software, and a user logs on to SQL SERVER: select your current server connection method and select the second one: Native login name and password, that is, your database role. For example, if the login name is SA, the password is 12345.