How to Improve Performance for Datetime Filtering in SQL Server

How to improve performance for datetime filtering in SQL Server?

Just a suggestion when it comes to indexes on datetime in msql is the index footprint impacts search times (Yes this seems obvious...but please read onward).

The importances to this when indexing on the datetime say for instance '2015-06-05 22:47:20.102' the index has to account for every place within the datetime. This becomes very large spatially and bulky. A successful approach that I've leveraged is create a new datetime column and populate the data by rounding the time to the hour and then building the index upon this new column. Example '2015-06-05 22:47:20.102' translates to '2015-06-05 22:00:00.000'. By taking this approach we leave the detailed data alone and can display it or use it by search on this new column which gives us approximately a 10x (at minimum) return on how fast results are returned. This is due to the fact that the index doesn't have to account for the minutes, seconds and millisecond fields.

How do I speed up a DateTime comparison query in SQL Server

Place an index on the date field, optionaly including the ID field if you need sorting or extra lookup base on the ID field. That is what an INDEX is for, to speed up lookup, sorting etc. Take a look at this article for more explanation: SQL Server Index Design Guide (technet.microsoft.com).

SQL Server 2008: Ordering by datetime is too slow

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 ids 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:

• Take all records from the table and sort them. Slowness is obvious.
• Use the index on 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:

• SQL Server: clustered index and ordering

---

Related Topics