1

I am trying to figure out why selecting rows within a time-range takes too much time.

I have one database table with more than 5 million rows.

There is a Non-Unique, Non-Clustered Index on the FooTimestamp column which is type of DateTimeOffset.

When I try to retrieve some rows within a time-range, it takes more than 2 minutes. (When I run it again it is relatively faster due to caching capabilities of MS-SQL Server 2008, I guess)

DECLARE @since datetimeoffset
DECLARE @before datetimeoffset

SET @since ='2013-03-20 00:00:00 +02:00'
SET @before ='2013-03-27 00:00:00 +02:00'

SELECT *
WHERE ([FooTimestamp] >= @since AND [FooTimestamp] <= @before)

Question: When it comes to retrieve rows from a table having millions of records, what should I do to get the query results faster? (I think 2 minutes for such a straight-forward query is too much)

pencilCake
  • 51,323
  • 85
  • 226
  • 363
  • 1
    Are the indexes fragmented? What is the query plan? – paparazzo Apr 10 '13 at 13:14
  • Can you make the index on `FooTimestamp` clustered? – muhmud Apr 10 '13 at 13:23
  • No; that column is not 100% sure to have unique values – pencilCake Apr 10 '13 at 13:35
  • Can you tell how many rows you are selecting vs count(*) from FooTimestamp table ? I think you have low selectivity and that's why you have in your execution plan Clustered index Scan operation. In this case, you can speed up your query with cover index (which index will be very expensive) - alter FooTimestamp index by adding all column that you want to extract in the include section( in your case - all the column from the table without the Clustered index column(s) ). – dferidarov Apr 10 '13 at 13:54
  • Other than Indexing, can you just check *BETWEEN* clause for the same query? – Zerotoinfinity Apr 10 '13 at 13:56

1 Answers1

0

If you want to optimize this particular query, I would create a clustered index on your FooTimeStamp column. The clustered index will allow SQL server to pull all of the row data using the index, without doing an additional lookup.

This will require you to drop any existing clustered index. You could add it back as a non-clustered index, if needed.

Contrary your comment, clustered indexes do not have to be unique. See also Do clustered indexes have to be unique?

Community
  • 1
  • 1
chue x
  • 18,573
  • 7
  • 56
  • 70