Indexing CreatedDate

Indexing columns is a joy and adventure.
I have just indexed a datetime column to boost performance. The query had a date range, and the column didn’t have index yet.

exec Event.ReadEvents @startDate='2012-11-19 00:07:30', @endDate='2012-11-19 00:07:35' 

The query took longer than a minute without index. Surprisingly and very happily, the time reduced to 6 milliseconds. I love index.

CREATE NONCLUSTERED INDEX [IX_Events_CreatedDate] ON [dbo].[Events]
(
	[CreatedDate] ASC
) ON [PRIMARY]

Another surprise. It took 6 minutes to create the index.

This is the stats.

I’m not sure which sort order (ASC or DESC) is better for datetime column. Maybe it’s just order preference, if your query requires sorted result.

Indexing CreatedDate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s