SQL 多行转列性能非常慢

Question

我在特定的 SQL 行到列转换方面遇到了严重的性能问题。单个查询最多需要一分钟才能提取 6 小时的数据。为了清楚起见，我简化了下表，但每天有超过一百万个数据点，因此表大小可能有所贡献。我对这样的复杂查询没有太多经验，所以我需要一些建议。数据表如下...

(数据点表)

id     datetime
_____________________
1      2015-09-08 21:00:00
2      2015-09-08 21:00:01
3      2015-09-08 21:00:02
4      2015-09-08 21:00:03
5      2015-09-08 21:00:04

(传感器数据表)

id     datapointId     SensorId     Temp    DateTime
_____________________________________________________
1      1               20           34.6    2015-09-08 21:00:00.345
2      1               21           34.2    2015-09-08 21:00:00.551
3      1               22           34.1    2015-09-08 21:00:00.101
4      1               41           34.3    2015-09-08 21:00:00.700
5      2               20           34.2    2015-09-08 21:00:01.223
6      2               21           34.4    2015-09-08 21:00:01.456
7      2               22           34.5    2015-09-08 21:00:01.100
8      2               41           34.6    2015-09-08 21:00:01.870

请注意，各个传感器的 DateTime 和 DataPoint DateTime 不相等(但它们很接近)。这就是为什么我使用第二个表，使我能够进行粗略的并排比较，而不必执行一些模糊的日期时间逻辑。

我需要使用如下所示的结果数据集查询此信息...

DateTime              Temp1    Temp2    Temp3
__________________________________________________
2015-09-08 21:00:00   34.5       34.1       41.1
2015-09-08 21:00:01   34.4       34.2       41.2
2015-09-08 21:00:02   34.4       34.2       41.2

我当前的 SQL 查询是这样(动态)编写的...

SELECT DataPoint.DateTime,
max(case when SensorData.SensorId = 20 then SensorData.Temp end) Temp1,
max(case when SensorData.SensorId = 21 then SensorData.Temp end) Temp2,
max(case when SensorData.SensorId = 22 then SensorData.Temp end) Temp3,
FROM DataPoint LEFT JOIN SensorData ON DataPoint.Id=dbo.SensorData.DataPointId
WHERE DataPoint.DateTime BETWEEN [x] and [y]
GROUP BY DataPoint.DateTime ORDER BY DataPoint.DateTime

所以我的问题分为三个部分:

1) 为什么这个特定的查询如此慢？

2)是否有更好的方法来存储我丢失的这些信息？目前我仍处于设计阶段。我选择此模式是因为我需要能够比较以不规则时间间隔触发数据的传感器的时间序列信息。

3)是否有更快的方法来查询这些数据并将其转换为我想要的格式？

编辑!!!抱歉，我忘记添加查询末尾有一个 GroupBy 子句。我的错。

Answer 1

• 首先您需要确保有 DataPointId 的索引，如果是 PK 可能有索引，但如果是 FK > 您必须手动添加它。

• 其次，您需要 SensorId 和 DateTime

  的索引

• 在查询之前第三次过滤SensorID，现在您正在处理所有百万条记录以生成报告

.

WHERE SensorID IN (20,21,22)

您也可以尝试PIVOT功能

<强> SqlFiddleDemo

SELECT [DateTime], [20] as Temp1, [21] as Temp2, [22] as Temp3
FROM
(SELECT [DateTime], SensorId, [Temp] 
 FROM sensor) AS SourceTable
PIVOT
(
MAX([Temp])
    FOR SensorId IN ([20], [21], [22])
) AS PivotTable;