With using a query on the table layout I have a performance problem:
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!