SQL Index Optimization method

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],'经理') OR CONTAINS([UserResume].[ResumeName],'简历'))

First execution: No indexes were established.

Execution effect:

Table ' worktable '. Scan count 0, logical read 0 times

Table ' Userresume '. Scan count 1, logical read 18,524 times

Table ' Workexperience '. Scan count 1, logical read 8,679 times

(1 rows affected)

SQL Server Execution Time:

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

Second execution:

View Execution Plan workexperience table is a table scan, establishing Ix_workexperience index (on ID column and the ID column of join reference).

Effect after adjustment:

Table ' Workexperience '. Scan count 1, logical read 1071 times

Table ' Userresume '. Scan count 1, logical read 18,524 times

(1 rows affected)

SQL Server Execution Time:

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

Third execution:

View execution plan, Userresume table is table scan, establish Ix_userresume index.

Effect after adjustment:

Table ' Workexperience '. Scan Count 11, logical read 48 times

Table ' Userresume '. Scan count 1, logical read 3,095 times

(1 rows affected)

SQL Server Execution Time:

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

Fourth time execution:

View the execution plan, after the Ix_userresume index scan, resulting in a reordering of the createtime columns, adjusting the createtime location and collation of the Ix_userresume index.

Effect after adjustment:

(50 rows affected)

Table ' Workexperience '. Scan Count 11, logical read 48 times

Table ' Userresume '. Scan count 1, logical read 3 times

(1 rows affected)

SQL Server Execution Time:

CPU time = 15 milliseconds, elapsed time = 404 milliseconds.