SQL optimization tool

SQL statements are the only way to operate databases and play a decisive role in database system performance. There are many ways to write SQL statements under the same condition, some of which often have a great impact on performance. But how can we ensure that high-performance SQL statements can be written when everyone has different levels of understanding the SQL language?
artificial intelligence automatic SQL optimization tools have emerged. Now I will introduce you to this tool: SQL tuning for SQL Server.
1. SQL tuning introduction
SQL turning is a tool in quest central software developed by quest.
quest Central (figure 1) is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle, DB2, and SQL Server databases. It includes the following tools:
· Database Management (DBA)
· database monitoring (Monitoring pack)
· database diagnosis (spotlight diagnostics)
· database analysis
· SQL tuning
· space management
· load generator)
· data generator
· PL/SQL Development (TOAD)
· Expert suggestion (Knowledge expert)
today, we will only introduce the usage of SQL optimization (SQL tuning for SQL Server.

Figure 1 quest central page
2. Optimize SQL using SQL Tuning
The following uses the SQL server's built-in northwind database as an example to help you understand how to use SQL tuning to optimize SQL.
(1) Establish a connection.
Select "SQL Server" in the "Database" tree on the main interface of Quest central, and then select the "SQL tuning" option in the "Tools" box that appears below, open the "lanch SQL tuning for SQL Server connections" dialog box (figure 2 ). We will establish a connection to the database server here, and further analysis will be completed on it.

Figure 2 "establish connection" dialog box
Double-click the new connection icon. In the displayed window, enter the database information, click OK, and then click Connect.
(2) analyze the original SQL statement.
Enter the original SQL statement to be analyzed in the "oriangal SQL" text box in the window. The Code is as follows:

/* Query the names of the same goods with different selling prices */
Select distinct C. companyName, P. productname
From [Order Details] od1, [Order Details] od2, orders O1, orders O2, customers C, products P
Where od1.unitprice <> od2.unitprice and od1.productid = od2.productid
And od1.orderid = o1.orderid
And od2.orderid = o2.orderid
And o1.customerid = o2.customerid
And o1.customerid = C. customerid

select a database in the upper-left corner of the interface, and click execute on the toolbar to execute the original SQL statement. SQL tuning automatically analyzes the SQL Execution Plan, the analysis result is displayed on the page (Figure 3 ).
Figure 3 analyze the original SQL statement
(3) Optimize the SQL statement.
click the "optimize statement" button on the toolbar to optimize the SQL statements, we can see that SQL tuning has produced 34 optimization solutions equivalent to the original SQL (figure 4 ).
figure 4 SQL optimization solution

(4) obtain the optimal SQL statement.
next, we will execute the above optimization scheme to select the equivalent SQL statement with the best performance. Select the optimization scheme to be executed in the list (all selected by default), click the drop-down menu next to the "execute" button on the toolbar, and select "execute selected ". After all the SQL statements are run, click the "Tuning resolution" button on the left side of the interface to see that the optimal SQL statements have come out, and the running time can be increased by 52%! (Figure 5)
Figure 5 "Tuning resolution" page
(5) learn to write expert SQL statements.
through the above steps, we can automatically optimize SQL statements, but more importantly, we can also learn how to write such high-performance SQL statements. Click the "compare scenarios" button on the left of the interface. We can compare the optimization scheme with any two SQL statements in the original SQL statement. SQL tuning will display the differences in different colors, you can also compare the execution plans of the two SQL statements in the "Execution Plan" below to understand the differences (figure 6 ).
figure 6 "compare scenarios" page
3. summary
the emergence of Artificial Intelligence automatic SQL optimization tools such as SQL tuning saves us a lot of time and energy. With the help of these tools, writing expert-level SQL statements is no longer difficult.