2 yu766588220 yu766588220 于 2016.03.06 19:00 提问

麻烦帮忙看下这个SQL查询该怎么改进 10C

有三张表,分别是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

1个回答

devmiao
devmiao   Ds   Rxr 2016.03.06 22:49
Csdn user default icon
上传中...
上传图片
插入图片