Artificial Intelligence automatic SQL Optimization tool--sqltuning for SQL Server_mssql

In view of this situation, artificial Intelligence automatic SQL Optimization tool came into being. Now I'm going to introduce you to this tool: Sqltuning for SQL Server.

1. SQL Tuning Introduction

SQL turning is a tool in the Quest Central software produced by Quest Corporation.

Questcentral (Figure 1) is an integrated, graphical, Cross-platform database management solution that can manage Oracle, DB2, and SQL Server databases simultaneously. It contains a number of tools as follows:

Database Management (DBA)
Database monitoring (Monitoring Pack)
Database Diagnostics (Spotlight diagnostics)
Database analysis
SQL optimizations (SQL tuning)
Spatial management (Space Management)
Stress test (Load generator)
Data Generation (generator)
Pl/sql Development (TOAD)
Expert advice (knowledge Expert)

Today, we'll just describe how to use SQL Optimizations (SQL tuning for SQL Server).

Figure 1 Quest The interface

2. Optimizing SQL Using SQL tuning

Let's use SQL Server's own Northwind database as an example to help you understand how to use sqltuning to optimize SQL.

(1) Establish a connection.
On the Questcentral main interface, select SQL Server on the Database tree, and then in the Tools box that appears below, select the Sqltuning option to open the Lanch SQL tuning for SQL Serverconnections dialog box (Figure 2). We set up a connection to the database server here, and future analytics will be done on it.

Figure 2 "Establish a Connection" dialog box

Double-click the "newconnection" icon to enter the information in the database in the pop-up window, clicking OK, and then clicking Connect.

(2) analyze the original SQL statement.
In the open window, in the "Oriangalsql" text box, enter the original SQL statement you want to analyze, the code is as follows:

/* Query selling price a different name of the same goods/
select DISTINCT c.companyname,p.productname from
[order Details] Od1,[order details] od2, Orders O1, orderso2,customers C, Products p
where od1. Unitprice<>od2. UnitPrice Andod1. Productid=od2. ProductID and
od1. Orderid=o1. OrderID and
od2. Orderid=o2. OrderID and
O1. Customerid=o2. CustomerID and
O1. Customerid=c.customerid

First select the database in the upper left of the interface, then click on the "Execute" button on the toolbar to execute the original SQL statement, sqltuning will automatically analyze the SQL execution plan and display the analysis results to the interface (Figure 3).

Figure 3 Parsing the original SQL statement

(3) Optimizing SQL.

Now we click on the "Optimize Statement" button on the toolbar to get sqltuning to start optimizing the SQL, and when you're done, you can see that sqltuning produces 34 optimizations that are equivalent to the original SQL (Figure 4).

Figure 4 SQL Optimization Scenario

(4) Obtain the optimal SQL.

Next, we will perform the optimization scenario above to select the best performance equivalent SQL statement. Select the optimization scheme you want to perform in the list (all selected by default), and then click the Drop-down menu next to the Execute button on the toolbar to select Executeselected. Wait until all SQL run complete, click on the left side of the interface "Tuningresolution" button, you can see the best SQL has come out, the running time can be increased by 52%! (Figure 5)

Figure 5 "Tuning Resolution" interface

(5) Learning to write expert-level SQL statements.

With the above steps, we can already implement automatic optimization of SQL statements, but more importantly, we can also learn how to write such high-performance SQL statements. Click on the "Comparescenarios" button to the left of the interface, we can compare the optimization scheme and the original SQL of any 2 SQL statements, sqltuning will be different between them in different colors expressed, but also in the lower "execution plan", Understand the differences between the execution plans of the two SQL statements (Figure 6).

Figure 6 "Compare Scenarios" interface

3. Summary

The advent of the

sqltuning Artificial Intelligence automatic SQL Optimization tool saves us a lot of time and effort. With the help of these tools, writing expert-level SQL statements will no longer be difficult.