Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use SQL Tuning for SQL Server, an automatic SQL optimization tool for artificial intelligence. The content of the article is of high quality, so Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

In response to this situation, artificial intelligence automatic SQL optimization tools came into being. SQL Tuning for SQL Server

1. SQL Tuning Introduction

SQL Turning is a tool in Quest Central software from Quest.

QuestCentral is an integrated, graphical, cross-platform database management solution that manages Oracle, DB2, and SQL server databases simultaneously. It includes several tools such as:

DBA Database Monitoring Pack Database Spotlight Diagnostics Database Analysis SQL Tuning Space Management Load Generator Data Generator PL/SQL TOAD Knowledge Expert

Today, we'll cover only the usage of SQL Tuning for SQL Server.

2. Optimizing SQL with SQL Tuning

Here we use SQL Server comes with Northwind database as an example to help you understand how to use SQLTuning to optimize SQL.

(1) Establish a connection. Select SQL Server on the Database tree on the main QuestCentral interface, and select SQL Tuning in the Tools box that appears below to open the Lanch SQL Tuning for SQL Server Connections dialog box. Here we establish a connection to the database server on which all future analysis work will be done.

Double-click the New Connection icon, enter information about the database in the pop-up window, 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 of the open window. The code is as follows:

/* Query the selling price of different names of the same good */select DISTINCT c.CompanyName,p.ProductNamefrom [Order Details] od1,[Order Details] od2 , Orders o1 , Orderso2,Customers c, Products pwhere od1.UnitPriceod2.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 database at the top left of the interface, and then click "Execute" button on the toolbar to execute the original SQL statement. SQLTuning will automatically analyze the SQL execution plan and display the analysis result on the interface.

(3) Optimize SQL.

Now we click on the Optimize Statement button on the toolbar to let SQLTuning start optimizing SQL. When it is finished, you can see that SQLTuning produces 34 optimization solutions equivalent to the original SQL.

(4) Get the best SQL.

Next, let's execute the optimization scenario generated above to select the best performing equivalent SQL statement. Select the optimization scheme to be executed in the list (all selected by default), then click the drop-down menu next to the "Execute" button on the toolbar and select "ExecuteSelected". After all SQL runs are completed, click the "TuningResolution" button on the left side of the interface to see that the optimal SQL has come out, and the runtime can be improved by 52%

(5) Learn to write expert SQL statements.

Through the above steps, we can already achieve automatic optimization of 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 any two SQL statements in the optimization scheme and the original SQL, SQLTuning will show the difference between them in different colors, and you can also compare the execution plan of two SQL statements in the "Execution Plan" below to understand the difference.

About artificial intelligence automatic sql optimization tool SQLTuning for SQL Server how to use it to share here, I hope the above content can have some help for everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.