Graphic tutorial on OracleSQLtuning database optimization steps

SQLTurning is a tool in QuestCentral software developed by Quest. QuestCentral is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle, DB2, and SQL Server databases. I. Introduction to SQLTuningforSQLServer the optimization of SQL statements is best for the database.

SQL Turning is a tool in Quest Central software developed by Quest. Quest Central is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle, DB2, and SQL server databases. I. Introduction to SQL Tuning for SQL Server the optimization of SQL statements gives full play to the database

SQL Turning is a tool in Quest Central software developed by Quest. Quest Central is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle, DB2, and SQL server databases.

I. Introduction to SQL Tuning for SQL Server

The optimization of SQL statements is critical to the optimal performance of the database. Unfortunately, application optimization is often ignored due to time and resource factors. The SQL Tuning (SQL optimization) module compares and evaluates the Running Performance of SQL statements in a specific application, and provides intelligent optimization suggestions to help users improve application response time. The SQL optimization module provides functions such as non-invasive SQL collection, automatic optimization, and expert suggestions to comprehensively improve SQL optimization.

Ii. Use of SQL Tuning for SQL Server

1. Open the Quest Database Management Solutions pop-up window 1, as shown in.

　　

2. enable SQL Tuning in red 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 (,). We will establish a connection to the database server here, and further analysis will be completed on it.

"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. After you click Connect, a new window is displayed.

　　

In the "Oriangal SQL" text box that opens the window, enter the original SQL statement to be analyzed, and select the corresponding database name in red. The SQL statement code is as follows:

Analyze original SQL statements

Original SQL statement

Click the Execute button on the toolbar to Execute the original SQL statement. SQL Tuning automatically analyzes the SQL Execution Plan and displays the analysis result on the interface ().

(3) Optimize SQL statements.

Now, click the "Optimize Statement" button on the toolbar to enable SQL Tuning to Optimize the SQL Statement. After completing the optimization, we can see that SQL Tuning has produced 19 optimization solutions equivalent to the original SQL Statement ().

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 running all the SQL statements, click the "Tuning Resolution" button on the left of the page,

We can see that the optimal SQL has come out, and the running time can be increased by 21%! ()

"Tuning Resolution" Interface

The optimal SQL statement is as follows:

5) learn to write expert-level SQL statements.

Optimized SQL statement

SELECT dbo. Person_BasicInfo .*,

Dbo. Graduater_GraduaterRegist.RegistNO AS RegistNO,

Dbo. Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,

Dbo. Graduater_GraduaterRegist.RegistMan AS RegistMan,

Dbo. Graduater_Business.ComeFrom AS ComeFrom,

Dbo. Graduater_Business.Code AS Code,

Dbo. Graduater_Business.Status AS Status,

Dbo. Graduater_Business.ApproveResult AS ApproveResult,

Dbo. Graduater_Business.NewCorp AS NewCorp,

Dbo. Graduater_Business.CommendNumber AS CommendNumber,

Dbo. Graduater_Business.EmployStatus AS EmployStatus,

Dbo. Graduater_Business.NewCommendTime AS NewCommendTime,

Dbo. Graduater_Business.GetSource AS GetSource,

Dbo. Graduater_Business.EmployTime AS EmployTime,

Dbo. Graduater_Business.Job AS Job,

Dbo. Graduater_Business.FillMan AS FillMan,

Dbo. Graduater_Business.FillTime AS FillTime,

Dbo. Graduater_Business.IsCommendOK AS IsCommendOK,

Dbo. Graduater_Business.ApproveUser AS ApproveUser,

Dbo. Graduater_Business.ApproveTime AS ApproveTime,

Dbo. Graduater_Business.RegistTime AS RegistTime,

Dbo. graduater_business.employee Corp AS employee Corp,

Dbo. Graduater_Business.JobRemark AS JobRemark,

Case when dbo. Graduater_Business.ComeFrom = 'ws 'then' online registration'

WHEN dbo. Graduater_Business.ComeFrom = 'hp 'then' UPC tower'

WHEN dbo. Graduater_Business.ComeFrom = 'jd 'then'

WHEN dbo. Graduater_Business.ComeFrom = 'mc 'then' Racecourse'

WHEN ComeFrom = 'zx' then' high finger center 'end AS ComeFromName,

Dbo. Person_Contact.Address AS Address,

Dbo. Person_Contact.Zip AS Zip,

Dbo. Person_Contact.Telephone AS Telephone,

Dbo. Person_Contact.Mobile AS Mobile,

Dbo. Person_Contact.Email AS Email,

Dbo. Person_Contact.IM as im,

Dbo. Person_Skill.ForeignLanguage AS ForeignLanguage,

Dbo. person_skill.foreign?agelevel AS foreign=agelevel,

Dbo. Person_Skill.CantoneseLevel AS CantoneseLevel,

Dbo. person_skill.manarinlevel AS manarinlevel,

Dbo. Person_Skill.Language AS Language,

Dbo. Person_Skill.TechnicalTitle AS TechnicalTitle,

Dbo. Person_Skill.ComputerLevel AS ComputerLevel,

Dbo. Person_EmployPurpose.JobType AS JobType,

Dbo. Person_EmployPurpose.Vocation AS Vocation,

Dbo. Person_EmployPurpose.JobPlace AS JobPlace,

Dbo. Person_EmployPurpose.Salary AS Salary,

Dbo. Person_EmployPurpose.OnJobDate AS OnJobDate,

Dbo. Person_EmployPurpose.CorpType AS CorpType,

Dbo. Person_EmployPurpose.Job AS RequireJob,

YEAR (GETDATE ()-YEAR (dbo. Person_BasicInfo.Birthday) AS Age,

Dbo. Graduater_Business.EmployType AS EmployType,

Dbo. Graduater_Business.EmployTypeCode AS EmployTypeCode,

Dbo. Graduater_Business.EmployCorpType AS EmployCorpType,

Case when dbo. Graduater_Business.PrintStatus = 'printed 'then' printed'

ELSE 'unprinted 'end AS PrintStatus,

Dbo. Graduater_Business.PrintTime AS PrintTime,

Case when dbo. Graduater_Business.EmployStatus = 'is 'then' already hired'

ELSE 'unemployed 'end as EmployStatusView

FROM dbo. Person_BasicInfo

Inner join dbo. Graduater_Business

ON dbo. Person_BasicInfo.PersonID = dbo. Graduater_Business.PersonID

Left outer join dbo. Graduater_GraduaterRegist

ON dbo. Graduater_Business.GradBusinessID = dbo. Graduater_GraduaterRegist.GraduaterGUID

Inner join dbo. Person_Contact

ON dbo. Person_BasicInfo.PersonID = dbo. Person_Contact.PersonID

Inner join dbo. Person_Skill

ON dbo. Person_BasicInfo.PersonID = dbo. Person_Skill.PersonID

Inner join dbo. Person_EmployPurpose

ON dbo. Person_BasicInfo.PersonID = dbo. Person_EmployPurpose.PersonID

OPTION (force order)

(