Comparing the speed

Question

Dear All,

I have one table "Absence" with the following field names: "StudentID (int), SubjectID (nvarchar(10)), CourseID (nvarchar(20)), Absence (int)" A primary index on three fields "StudentID, SubjectID, CourseID". Please note that StudentID is an INTEGER field and both SubjectID and CourseID are NVARCHAR fields.

Comparing the speed, which is faster?

1. Select top 1 * from Absence order by StudentID, SubjectID, CourseID
2. Select top 1 * from Absence order by StudentID, CourseID, SubjectID

Answer 1

There are many variables in the query plan which might affect how "fast" a query runs. You can test it yourself by using the "actual execution" button in SSMS.

In your example, you said "primary index" I assume you mean clustered index. If so, in theory 1 should be faster because it will not need a sort on StudentID, CourseID, SubjectID to find the top 1.

However, unless you have billions of rows and many duplicate CourseID, SubjectID, in reality, you are not going to see any difference with a table row allocation that small. Because, SQL Server reads 8K pages, not a row.

Answer 2

In theory the first one should be faster, but you may want to test performance.