Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Performance issue filtering on date/time field

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

Comments

Sign In or Register to comment.