indexing datetime column on a very wide table

Question

I have table with about 400 columns and 4 million rows in SQL Server 2012. the only purpose of this table to be used by a reporting tool. this table is refreshed(dropped and recreated) every night via scheduled Job. so no update/insert/delete. there is a Date column with Datetime as datatype. I have created a clustered index on this date column but it only seemed to help a little.(there wont be any other conditions on where clause so I haven't included any other columns in the index) the query send by reporting tool is like

select *(all columns listed) from mytable where date>='01/01/2010' and date <='12/01/2010'

it takes about 10 mins to retrieve all that falls under above date range which is about a million rows.

I need to get this under a minute if I can or the best I can. if I can get some idea that might help me to achieve this . I would greatly appreciate it.

I have tried following but no significant performance gain. -change datatype to 'Date'/'varchar'/'int' from 'Datetime' -create nonclustered index on same column -create clustered/nonclustered index including other columns to make it unique

Have you looked into the general performance of your server, e.g. using Resource Monitor? This may be a general performance issue: insufficient memory, not enough I/O bandwidth to the disk(s), ... . At least lock contention shouldn't be much of a problem if you are only running one report at a time. — HABO

paparazzo paparazzo · Accepted Answer · 2015-12-10T22:27:37

100 million rows may just plain be a data volume thing.
Try:

select count(*) from mytable where date>='01/01/2010' and date <='12/01/2010'

If that is fast then it is not an index issue

indexing datetime column on a very wide table

1 Answers