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

This article mainly explains the "SQL index optimization method", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "SQL index optimization method" bar!

SELECT TOP 50

ROW_NUMBER () OVER (ORDER BY ResumeCreateTime DESC) as [RowID]

, [TopDegree]

, [DegreeRankID]

, [UserResume]. [UserResumeID]

, [UserResume]. [UserID]

, [ResumeName]

, [BirthDate]

, [WorkStartedDate]

, [SalaryNeeded]

, [BufferTimeSpanID]

, [ResumeCreateTime]

FROM [dbo]. [UserResume] INNER JOIN [dbo]. [WorkExperience] ON [WorkExperience]. [UserResumeID] = [UserResume]. [UserResumeID]

WHERE

(CONTAINS ([WorkExperience]. [WorkSummary], manager) OR CONTAINS ([UserResume]. [ResumeName], resume))

First execution: no indexes are built.

Execution effect:

Table Worktable. Scan count 0, logic read 0 times

Table UserResume. Scan count 1, logic read 18524 times

Table WorkExperience. Scan count 1, logic read 8679 times

(1 line affected)

SQLServer execution time:

CPU time = 2152 milliseconds, occupancy time = 3126 milliseconds.

Second execution:

To view the execution plan, the WorkExperience table is a table scan that builds an IX_WorkExperience index (on the ID column and the ID column referenced by the JOIN).

Adjusted effect:

Table WorkExperience. Scan count 1, logic read 1071 times

Table UserResume. Scan count 1, logic read 18524 times

(1 line affected)

SQL Server execution time:

CPU time = 1638 milliseconds, occupancy time = 2045 milliseconds.

Third execution:

To view the execution plan, the UserResume table is a table scan to build the IX_UserResume index.

Adjusted effect:

Table WorkExperience. Scan count 11, logic read 48 times

Table UserResume. Scan count 1, logic read 3095 times

(1 line affected)

SQL Server execution time:

CPU time = 1248 milliseconds, occupancy time = 1568 milliseconds.

Fourth execution:

Looking at the execution plan, after the IX_UserResume index scan, there is a reorder of the createtime column, adjusting the location and collation of the createtime in the IX_UserResume index.

Adjusted effect:

(50 rows affected)

Table WorkExperience. Scan count 11, logic read 48 times

Table UserResume. Scan count 1, logic read 3 times

(1 line affected)

SQL Server execution time:

CPU time = 15ms, occupancy time = 404ms.

Thank you for your reading, the above is the content of "the optimization method of SQL index", after the study of this article, I believe you have a deeper understanding of the optimization method of SQL index, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.