Cluster index on datetime column
-
I am developing a sql server database for a taxi company.
Every record in the bookings table has a booking date and time as one column.
These bookings are obviously not unique values in fact far from it.
I want to speed up access to old data using a index. I normally input the date of the records I wish to access
or a particular week.
Should i use a clustered index or not.
Any ideas.
Thanks
Paul
-
Probably < grin > it's almost textbook question. I usually designate clustered index where it resolves my worst performance \ most important query. If you've got an ascending key on the table then making that the second column may improve things. Basically if you can get your clustered index to act like a HEAP then that can assist in avoiding page splits on inserts - especially as you say the column may not be unique. I often find clustered indexes to be useful in reducing i/o on non unique columns - this might not be the "textbook view" but if it speeds up queries, reduces i/o and contention, then it's a good move.
As always, test, change, test, compare, test again.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Ok thanks for that. I will do some testing.
-
Continueing on ths subject.....
I have just inherited 2 audit tables (tbl1 +- 3 milj rows, tbl2 +- 70 mil rows) with no indexes apart from a single clustered PK on each
Being audit tables, no updates will take place, but plenty of inserts and the occasional select(+-10 a day).
Most, if not all of the selects happens on a join of the two tables and contains a datetime column from tbl1 in the where clause. These never complete under 10 minutes - no surprise there
A common query would look as follows:
SELECT *
FROM tbl1
INNER JOIN tbl2 on tbl2.idref = tbl1.id (there is no FK relationship, but tbl2 does contain multiple records "linking" itself to tbl1's PK - which should have been implemented as a one-to-many relationship)
WHERE tbl1.datetimecol between 'xxx' and 'xxx'
My questions:
Is it worth creating a non-clustered index on type datetime? I have recently read an article which stated that indexes on datetime columns are usually not required?!
If so, could it be beneficial to move the clustered index from a int(PK + idenity) to a datetime column in the same table if the datetime column is ALWAYS getting used in the selects? Or would datetime not benefit from the clustered index's sort order as much as as an int would?
I know the best way of seeing what works in my situation would be to test, test and then test some more, but I would really like to get your opinions/do's and don'ts from your index configurations, especially datetime columns.
Thanks!
What's this "backup strategy" everyone is on about? -
The general wisdom is that clustered indexes are best when one does a range query, so the datetimecol may indeed be a good candidate for a clustered index. But, yes, you will have to test this to make sure of the proper configuration for you environment. Performance tuning is more of an art than a science.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply