My table (SQL Server 2008) has 1 million+ records, when I try to order records by datetime, it takes 1 second, but when I order by ID (int), it only takes about 0.1 second.
Is there any way to improve the efficiency? (I already added the datetime column to the index)
Ordering by id
probably uses a clustered index scan while ordering by datetime
uses either sorting or index lookup.
Both these methods are more slow than a clustered index scan.
If your table is clustered by id
, basically it means it is already sorted. The records are contained in a B+Tree
which has a linked list linking the pages in id
order. The engine should just traverse the linked list to get the records ordered by id
.
If the id
s were inserted in sequential order, this means that the physical order of the rows will match the logical order and the clustered index scan will be yet faster.
If you want your records to be ordered by datetime
, there are two options:
datetime
. The index is stored in a separate space of the disk, this means the engine needs to shuttle between the index pages and table pages in a nested loop. It is more slow too.To improve the ordering, you can create a separate covering index on datetime
:
CREATE INDEX ix_mytable_datetime ON mytable (datetime) INCLUDE (field1, field2, …)
, and include all columns you use in your query into that index.
This index is like a shadow copy of your table but with data sorted in different order.
This will allow to get rid of the key lookups (since the index contains all data) which will make ordering by datetime
as fast as that on id
.
Update:
A fresh blog post on this problem:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With