加速SQL查询

Question

我有一个查询，在查询超过最近几个小时之前的数据时，需要花费较长时间来执行。这个查询将用于数据挖掘，所以期望它能够搜索数周或数月的数据，并在合理的时间内返回结果（即使几分钟也可以……我运行了一个日期范围从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

- Tom

---

我认为这是一个可以接受的问题。但是，您需要发布您的表模式以及任何已设置的索引信息。 - Josh Darnell

最明显的第一个问题 - Rownumber INCLUDE (value) 是否有索引？ - JNK

另外，你知道CTE只是一种可丢弃的视图，对吧？这并没有性能上的好处。你基本上有三个嵌套视图级别在这里聚合... - JNK

http://stackoverflow.com/questions/5456509/tips-and-tricks-to-speed-up-an-sql - Soner Gönül

@Tom - 看起来你无法对其进行索引，这可能是问题的根源。你可以考虑将其实现为一个 #TEMP 表，对其进行索引，然后查询那些 #temp 表。即使存在表创建/索引开销，它仍然可能比 CTE 解决方案快得多。 - JNK

Answer 1

不知道领域是什么，很难理解您的查询目的。但是，我认为您的查询只是试图查找dbo.some_table中每行sub_id不为0的记录的以下列的范围（或者如果只有一个匹配项，则为单个值）：

• 当LO_SENSOR_ALARM=0时，LO_AVG
• 当LC_SENSOR_ALARM=0时，LC_AVG
• 当RO_SENSOR_ALARM=0时，RO_AVG
• 当RC_SENSOR_ALARM=0时，RC_AVG

您构建了此查询，为每行分配了顺序行号，展开了带有其行号的_Avg列，通过行号计算了范围聚合，并通过行号重新加入到原始记录中。 CTE不会实现结果（如评论中所述，也不会索引）。因此，对AddRowNumbers的每个引用都很昂贵，因为ROW_NUMBER() OVER (ORDER BY LO_Avg)是一种排序。

不要将此表切成碎片，然后再按行号将其拼接回来，为什么不做类似以下操作：

SELECT cds.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

   --if the COUNT is 0, xweb_range will be null (since MAX will be null), if it's 1, then use MAX, else use MAX - MIN (as per your example)
   , (CASE WHEN stats.[Count] < 2 THEN stats.[MAX] ELSE stats.[MAX] - stats.[MIN] END) xweb_range

FROM dbo.some_table cds

    --cross join on the following table derived from values in cds - it will always contain 1 record per row of cds
    CROSS APPLY
    (
        SELECT COUNT(*), MIN(Value), MAX(Value)
        FROM
        (
            --construct a table using the column values from cds we wish to aggregate
            VALUES (LO_AVG, LO_SENSOR_ALARM),
                   (LC_AVG, LC_SENSOR_ALARM),
                   (RO_AVG, RO_SENSORALARM),
                   (RC_AVG, RC_SENSOR_ALARM)


        ) x (Value, Sensor_Alarm) --give a name to the columns for _AVG and _ALARM
        WHERE Sensor_Alarm = 0 --filter our constructed table where _ALARM=0

    ) stats([Count], [Min], [Max]) --give our derived table and its columns some names

WHERE cds.sub_id <> '0' --this is a filter carried over from the first CTE in your example

- Michael Petito

---

感谢您在此方面的努力。我想尝试这种方法，但是我在VALUES附近遇到了错误。我不确定代码的那部分的正确语法，因为我从未见过像您所做的那样的东西。这种技术叫什么？ - Tom

你正在使用SQL Server 2008吗？VALUES关键字是表值构造函数。文档在这里：http://technet.microsoft.com/en-us/library/dd776382.aspx，你可以在示例C中看到它作为派生表在`FROM`子句中的类似用法。 - Michael Petito

此外，如果您无法使用“VALUES”，可能是由于您的SQL兼容级别设置为100（SQL 2008）以外的某个值。您还可以将“VALUES…”替换为“SELECT LO_AVG，LO_SENSOR_ALARM UNION ALL SELECT LC_AVG，LC_SENSOR_ALARM UNION ALL …” - Michael Petito

太棒了，@Michael。最终我使用了SELECT的UNION而不是VALUES。不确定为什么VALUES对我无效，我使用的是SQL 2008。这个查询从昨天的数据需要运行5分钟以上，变成了在一个月前的数据上只需几秒钟就能运行完毕。非常感谢。 - Tom