我有一个查询,在查询超过最近几个小时之前的数据时,需要花费较长时间来执行。这个查询将用于数据挖掘,所以期望它能够搜索数周或数月的数据,并在合理的时间内返回结果(即使几分钟也可以……我运行了一个日期范围从
问题出在底部两个
这个查询全部都来自一张表。这个查询与原始表格唯一不同的地方就是列
2011年10月3日下午12:00
到2011年10月3日下午1:00
的查询,耗时44分钟!)问题出在底部两个
LEFT OUTER JOIN
。如果将它们删除,查询只需要大约10秒就能够执行完毕。然而,它们是这个查询的核心。这个查询全部都来自一张表。这个查询与原始表格唯一不同的地方就是列
xweb_range
。 xweb_range
是一个计算字段列(范围),它只使用[LO,LC,RO,RC]_Avg
的值来计算范围;当对应的[LO,LC,RO,RC]_Sensor_Alarm = 0
时(如果传感器报警= 1,则不包括在范围计算中)。WITH Alarm (sub_id,
LO_Avg, LO_Sensor_Alarm, LC_Avg, LC_Sensor_Alarm, RO_Avg, RO_Sensor_Alarm, RC_Avg, RC_Sensor_Alarm) AS (
SELECT sub_id, LO_Avg, LO_Sensor_Alarm, LC_Avg, LC_Sensor_Alarm, RO_Avg, RO_Sensor_Alarm, RC_Avg, RC_Sensor_Alarm
FROM dbo.some_table
where sub_id <> '0'
)
, AddRowNumbers AS (
SELECT rowNumber = ROW_NUMBER() OVER (ORDER BY LO_Avg)
, sub_id
, LO_Avg, LO_Sensor_Alarm
, LC_Avg, LC_Sensor_Alarm
, RO_Avg, RO_Sensor_Alarm
, RC_Avg, RC_Sensor_Alarm
FROM Alarm
)
, UnPivotColumns AS (
SELECT rowNumber, value = LO_Avg FROM AddRowNumbers WHERE LO_Sensor_Alarm = 0
UNION ALL SELECT rowNumber, LC_Avg FROM AddRowNumbers WHERE LC_Sensor_Alarm = 0
UNION ALL SELECT rowNumber, RO_Avg FROM AddRowNumbers WHERE RO_Sensor_Alarm = 0
UNION ALL SELECT rowNumber, RC_Avg FROM AddRowNumbers WHERE RC_Sensor_Alarm = 0
)
SELECT rowNumber.sub_id
, cds.equipment_id
, cds.read_time
, cds.LC_Avg
, cds.LC_Dev
, cds.LC_Ref_Gap
, cds.LC_Sensor_Alarm
, cds.LO_Avg
, cds.LO_Dev
, cds.LO_Ref_Gap
, cds.LO_Sensor_Alarm
, cds.RC_Avg
, cds.RC_Dev
, cds.RC_Ref_Gap
, cds.RC_Sensor_Alarm
, cds.RO_Avg
, cds.RO_Dev
, cds.RO_Ref_Gap
, cds.RO_Sensor_Alarm
, COALESCE(range1.range, range2.range) AS xweb_range
FROM AddRowNumbers rowNumber
LEFT OUTER JOIN (SELECT rowNumber, range = MAX(value) - MIN(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) > 1) range1 ON range1.rowNumber = rowNumber.rowNumber
LEFT OUTER JOIN (SELECT rowNumber, range = AVG(value) FROM UnPivotColumns GROUP BY rowNumber HAVING COUNT(*) = 1) range2 ON range2.rowNumber = rowNumber.rowNumber
INNER JOIN dbo.some_table cds
ON rowNumber.sub_id = cds.sub_id
Rownumber INCLUDE (value)
是否有索引? - JNKCTE
只是一种可丢弃的视图,对吧?这并没有性能上的好处。你基本上有三个嵌套视图级别在这里聚合... - JNK#TEMP
表,对其进行索引,然后查询那些#temp
表。即使存在表创建/索引开销,它仍然可能比 CTE 解决方案快得多。 - JNK