有三张表,分别是EquipmentMessage,Sensor,SensorInputData,Sensor以
EquipmentMessage的主键equipmentId做外键,SensorInputData则以Sensor的主键sensorId做外键。SensorInputData是大表,可能有千万条数据,另外两个都是小表,最多不会超过200条数据。我的这个查询第二句是一个“*”号,此时查询速度很快,大概170ms,如果我只想要其中个别字段,查询速度极慢,需要8s。现在我就想要其中个别字段,该怎么改进
select
* --这里用“*”查询很快,但是如果改成equipment.equipmentName就很慢了
from
(
select
sensorData.valueType+':'+(Convert(varchar(20),round(sensorData.value,2)))+sensor.sensorUnit as 数据,
Convert(varchar(20),sensorData.date,120) as 日期,
sensor.equipmentId as ref_equipmentId
from Sensor as sensor
join SensorInputData as sensorData on sensor.sensorId=sensorData.sensorId
and not exists
(
select 1 from SensorInputData as t where t.sensorId=sensorData.sensorId and t.date>sensorData.date
)
) sensorData join EquipmentMessage as equipment
on equipment.equipmentId=sensorData.ref_equipmentId