Artificial Intelligence automatic SQL optimization tools-SQLTuning for SQL Server, SQL-sqltuning

Artificial Intelligence automatic SQL optimization tools-SQLTuning for SQL Server, SQL-sqltuning

In response to this situation, the artificial intelligence automatic SQL optimization tool came into being. Now I will introduce this tool: SQLTuning for SQL Server.

1. SQL Tuning Introduction

SQL Turning is a tool in Quest Central software developed by Quest.

QuestCentral (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 contains the following tools:

Database Management (DBA)
Database Monitoring (Monitoring Pack)
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 how to use 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 SQLTuning to optimize SQL.

(1) Establish a connection.
Select SQLServer on the "Database" tree on the main interface of QuestCentral, and then select the "SQLTuning" option in the "Tools" box that appears below, open the "Lanch SQL Tuning for SQL ServerConnections" 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 "NewConnection" 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 "OriangalSQL" 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. productNamefrom [Order Details] od1, [Order Details] od2, Orders o1, Orderso2, Customers c, products pwhere od1.UnitPrice <> od2.UnitPrice andod1.ProductID = od2.ProductIDand od1.OrderID = o1.OrderIDand od2.OrderID = o2.OrderIDand o1.CustomerID = o2.CustomerIDand 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. SQLTuning 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 SQL statements.

Now, click the "Optimize Statement" button on the toolbar to enable SQLTuning to Optimize the SQL Statement. After completing the optimization, we can see that SQLTuning has produced 34 optimization solutions equivalent to the original SQL Statement (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 "ExecuteSelected ". After all the SQL statements are run, click the "TuningResolution" 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 Interface

(5) learn to write expert-level 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 "CompareScenarios" button on the left of the interface. We can compare the optimization scheme with any two SQL statements in the original SQL statement. SQLTuning 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 automatic SQL optimization tools such as SQLTuning saves us a lot of time and energy. With the help of these tools, writing expert-level SQL statements is no longer difficult.

How can I optimize SQL statements?

You can write executable SQL statements in the database application system in multiple ways, but it is difficult to determine which one is the best solution. To solve this problem, it is necessary to optimize SQL. Simply put, the optimization of SQL statements is to convert low-performance SQL statements into SQL statements with better performance for the same purpose.

Reasons for optimizing SQL statements

The lifecycle of a database system can be divided into three stages: design, development, and product. Optimization at the design stage has the lowest cost and the greatest benefit. Optimization in the finished stage has the highest cost and the lowest benefit. If a database system is compared to a building, correction after the building is built is often costly and has little effect (or even cannot be corrected ), in the building design and production stages, controlling the quality of each brick and tile can achieve the goal of low cost and high effectiveness.

To maximize the benefits, we often need to optimize the database. Database optimization can usually be performed by optimizing the network, hardware, operating system, database parameters, and applications. According to statistics, the performance improvement obtained by optimizing network, hardware, operating system, and database parameters only accounts for about 40% of the database application system performance improvement, the other 60% of system performance improvements come from application optimization. Many optimization experts even believe that application optimization can improve system performance by 80%. Therefore, it is certain that optimization of the database system by optimizing the application can achieve greater benefits.

Application optimization can be divided into two aspects: source code optimization and SQL statement optimization. Due to changes in program logic, source code optimization is costly in terms of time cost and risk (especially for systems in use ). On the other hand, source code optimization has limited effect on improving the database system performance, because the database operations performed by applications are ultimately performed by SQL statements on the database.

There are some direct reasons for optimizing SQL statements:

1. SQL statements are the only way to operate databases (data). The execution of applications is ultimately attributed to the execution of SQL statements, the efficiency of SQL statements plays a decisive role in the performance of the database system.

2. SQL statements consume 70% ~ 90% of database resources.

3. SQL statements are independent of program design logic. Optimization of SQL statements does not affect program logic. Compared with optimization of program source code, the cost of optimizing SQL statements is low in both time and risk.

4. SQL statements can be written in different ways. The performance of different statements may vary greatly.

5. SQL statements are easy to learn and difficult to master. The performance of SQL statements is often related to the database structure and number of records of the actual running system. There is no general rule to improve the performance.

Traditional Optimization Methods

Traditionally, SQL programmers use manual rewriting to optimize SQL statements. This mainly relies on DBA or senior programmers to analyze the SQL statement execution plan, rely on experience, try to rewrite the SQL statement, and then compare the results and performance to try to find the SQL statement with better performance. This practice has the following shortcomings:

1. All possible SQL statements cannot be written. It may take a lot of time to find SQL statements with better performance. Even if an SQL statement with better performance is found, you cannot know whether there is a better performance statement.

2. It is very dependent on human experience. The amount of experience often determines the performance of the optimized SQL statement.

3. very time-consuming. Rewrite --> verify correctness --> compare performance. This cycle takes a lot of time.

According to the functions of traditional SQL optimization tools, optimization tools are generally divided into the following three generations:

The first generation of SQL optimization tools is execution plan analysis tools. 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. This type of tool has a fatal disadvantage: Only one SQL statement is analyzed and the conclusion of adding an index is obtained, which is ignored (in fact, it cannot be evaluated) the impact of the added index on the overall database system performance.

... The remaining full text>

How can I optimize SQL statements?

There are many reasons for slow query speed. The following are common causes:

1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)

2. Low I/O throughput, resulting in a bottleneck effect.

3. the query is not optimized because no computing column is created.

4. Insufficient memory

5. slow network speed

6. The queried data volume is too large (you can use multiple queries to reduce the data volume in other ways)

7. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming)

8. sp_lock and sp_who are active users. The reason is that they read and write competing resources.

9. Unnecessary rows and columns are returned.

10. The query statement is not good and is not optimized.