CODEWITHSUNDEEP
sqlsql-server-2012
Fred_F
Fred_F

Reputation: 53

Improve sql query performance by using ordering?

I have a SQLServer database with tables containing temperature measurements.

The tables have columns for MeasurementId(prim key), SensorId, Timestamp and Value.

We now have have enough measurements that our queries are starting to get a little bit slow and I'm trying to improve this.

The Timestamp values are not necessarily in order, but for each SensorId they are ordered. My question is: Is there anyway I can use this knowlegde to improve the performance of a query like SELECT * FROM MeasurmentTable WHERE SensorId=xx AND Timestamp>yy ?

I.e, can i hint to SQL Server that once you've narrowed your results to a unique SensorId, the rows are guaranteed to be ordered by timestamp?

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

For your query, you simply want a composite index:

create index idx_measurementtable_sensorid_timestamp
    on MeasurementTable(SensorId, Timestamp);

Upvotes: 2

Related Questions