Performance issue filtering on date/time field

Kanariepieter

Dear all,

With using a query on the table layout I have a performance problem:
-Table name:
Values
-Columns:
Values_LogTime (Date/Time)
Datapoint_ID (Interger - Foreign key)
Values_Value (Numeric 12 - 4)
-Indexes on Datapoint_ID and Values_LogTime

To report this data I'm using the following query (as example):
SELECT Values_LogTime,Values_Value FROM Values WHERE
Values_LogTime BETWEEN '2004-11-11 16:00:00' AND '2004-11-11 18:00:00'

The performance problem occurs when there are over 5.000.000 records in the table and when the timeframe is between the minimum and maximum timestamp.

When I perform this query, it will take SQL server 2000 about 25 seconds when I'm performing the query from the "Query analyzer". I need to get the performance down to about 5 seconds at most.
I have considered using a bigint for the timestamp to improve the performance, however this will cause a lot of additional programming to handle this.

Does anybody have some ideas how to improve the table layout / query to get an optimal performance? Its hard to find any info on filtering on time ranges so I would be very grateful if anybody can help out!

Thanx, Pieter

Shanoo

Try creating a covering index on Values_LogTime and Values_Value columns.