April 18th, 2012, 05:19 PM
Join Date: Mar 2004
Time spent in forums: 4 h 40 m 3 sec
Reputation Power: 0
Can this self-join be optimized?
here's the query:
from sensor_data sd1
left join sensor_data sd2 on
sd1.sensor_id = sd2.sensor_id and
sd1.sequence_id < sd2.sequence_id
where sd1.sensor_status = 1
group by sd1.sensor_id, sd1.sequence_id
the sensor_data table has 4 columns:
[PK]sensor_id (varchar), sensor_status (int), sensor_time (datetime), [PK]sequence_id (int)
The purpose of the query is to essentially join each row with it's own next row per sensor_id. The size of the table is about 360,000. how can I make this more efficient?