Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2011-04-06 : 09:49:37
|
| Hello,I have a table with historical data over time.Each row has a STARTDATE and ENDDATE (in datetime format).Current rows have an enddate set to 2999-01-01.So you might see something like this:STARTDATE ENDDATE SSN DATA1 DATA22005-01-01 2008-03-05 0000000001 BLAH BLAH22005-03-05 2010-07-26 0000000001 BLOO BLAH22010-07-26 2999-01-01 0000000001 BLUB1 BLUB2The table will have hundreds of thousands, perhaps millions of rows.Most often I'm intersted in the current row:select * from table where getdate() between startdate and enddateand SSN = '0000000001'Sometimes I want to find historical data so I'll use a specific date rather than getdate().I think I want a clustered index on SSN, what is the best index to put on the date columns to optimize data retrieval when using BETWEEN?Thanks for any help you can give! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-04-06 : 09:54:34
|
| If you're clustering on SSN, put nonclustered indexes on startdate and enddate.But you should test different indexing schemes (including composite indexes). |
 |
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2011-04-06 : 13:52:34
|
| Well I added three indexes.1. on SSN (clustered)2. On startdate descending3. On enddate descendingI'm pleased with the performance so far, with about 1/2 million rows.A look at the execution plan shows a single clustered index seek (on the SSN) if I include SSN as a WHERE criterion.If I pull all current rows (WHERE getdate() BETWEEN startdate and enddate) I see it using a clustered index scan, not sure if this can be improved upon. Takes about 1/2 second in 'CPU cost'. |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-06 : 16:54:19
|
| If there is not too many rows per ssn (like tens of 1000s), then cluster index on ssn is quite sufficient.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-04-07 : 03:50:17
|
| And did you consider/try the compound indices below?SSN & startdateSSN & enddateSSN & startdate & enddate |
|
|
|
|
|
|
index where getdate() between start and end