多个OUTER APPLY查询变慢，提高查询速度的最佳方法是什么？

Question

我正在尝试进行大量的表格数据整理，其中单个记录需要根据相同的表格和以前的记录按日期整理数据。

目前，我有6个OUTER APPLY，每次运行1个日期大约需要3分钟。我可能需要50多个计算字段和多个日期，因此这开始看起来不可行。

是否有更好的方法来提高查询速度？

DECLARE @Date datetime;
SET @Date = '2018-01-01';

SELECT * --Not real Select, set as * to simplify

-- Following subquery normally contains methods to clean data 
FROM (SELECT t1.* FROM  (SELECT cleanFields1.* FROM Control AS cleanFields1 
WHERE cleanFields1.[QDate] = @Date) AS t1) t1 

-- Calculated Data

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountMonth
    FROM Control t6
    WHERE t6.[ProductName] = t1.[ProductName]
    AND t6.[QDate] < t1.[QDate]
    AND MONTH(t6.[QDate]) = MONTH(t1.[QDate])
) t6

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchMonth
    FROM Control t7
    WHERE t7.[ProductName] = t1.[ProductName]
    AND t7.[QDate] < t1.[QDate]
    AND t7.[Issue] = '1'
    AND MONTH(t7.[QDate]) = MONTH(t1.[QDate])
) t7

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountArea
    FROM Control t8
    WHERE t8.[ProductName] = t1.[ProductName]
    AND t8.[QDate] < t1.[QDate]
    AND t8.[AreaName] = t1.[AreaName]
) t8

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchArea
    FROM Control t9
    WHERE t9.[ProductName] = t1.[ProductName]
    AND t9.[QDate] < t1.[QDate]
    AND t9.[Issue] = '1'
    AND t9.[AreaName] = t1.[AreaName]
) t9

OUTER APPLY (
    SELECT COUNT(*) AS ProductCountPType
    FROM Control t10
    WHERE t10.[ProductName] = t1.[ProductName]
    AND t10.[QDate] < t1.[QDate]
    AND t10.[PType] = t1.[PType]
) t10

OUTER APPLY (
    SELECT COUNT(*) AS ProductMatchPType
    FROM Control t11
    WHERE t11.[ProductName] = t1.[ProductName]
    AND t11.[QDate] < t1.[QDate]
    AND t11.[Issue] = '1'
    AND t11.[PType] = t1.[PType]
) t11

编辑:

SQLFiddle: http://sqlfiddle.com/#!18/9541d/1

期望的输出：

- user3904868

---

最好提供输入数据和期望结果。有人可能会有完全不同于您的方法的答案。 - Joe C

所有这些外部应用程序可能可以简化为一个单独的连接，使用一些情况表达式来生成所需的聚合值。您目前拥有的子查询是不可搜索的，因为您已将QDate列包装在函数中。向我们展示表格，我们可以帮助重新考虑这个问题，并使其快速。 - Sean Lange

感谢提供样本数据和期望输出。您能解释一下您想要做什么吗？您的代码在这里似乎过于复杂，难以理解。 - Sean Lange

简单来说，我想要计算当前记录（i）之前出现的总ProductName数量，并且在其中还要再次计数那些Issue = 1的数量(ii)。随后我将使用它来推导出百分比(ii) / i)。然而，如果存在额外条件，例如匹配月份、匹配PType等，则有各种变化。第一个子查询通常包含额外语句以清理数据以减少重复。 - user3904868

展示查询计划。 - RBarryYoung

Answer 1

您可以消除所有的交叉应用，这将极大地提高性能。此外，当Issue是int类型时，请避免使用'1'这样的内容，应该使用1。

在这种情况下，我使用了cte来展示如何隔离要返回的行。从那里只需要进行一些条件聚合即可。

DECLARE @Date datetime = '2018-01-01';

with CurrentRows as
(
    select *
    from Control c
    where c.QDate = @Date
)

select cr.*
    , ProductCountMonth = sum(case when MONTH(c.QDate) = MONTH(cr.QDate) then 1 else 0 end)
    , ProductMatchMonth = sum(case when MONTH(c.QDate) = MONTH(cr.QDate) AND c.Issue = 1 then 1 else 0 end)
    , ProductCountArea = sum(case when c.AreaName = cr.AreaName then 1 else 0 end)
    , ProductMatchArea = sum(case when c.Issue = 1 and c.AreaName = cr.AreaName then 1 else 0 end)
    , ProductCountPType = sum(case when c.PType = cr.PType then 1 else 0 end)
    , ProductMatchPType = sum(case when c.PType = cr.PType and c.Issue = 1 then 1 else 0 end)
from CurrentRows cr
join Control c on c.QDate < cr.QDate and c.ProductName = cr.ProductName
group by cr.QDate
    , cr.ProductName
    , cr.AreaName
    , cr.PType
    , cr.Issue
order by cr.AreaName

- Sean Lange

---

不错的 +1。这可能是最快的解决方案。但你有一个小错误。它必须是 MONTH(c.QDate) = MONTH(cr.QDate)。 - uzi

谢谢 @uzi。已经解决了。 - Sean Lange

你的ProductMatchArea计数缺少c.AreaName = cr.AreaName。 - JamieD77

@JamieD77 已修复。 - Sean Lange

谢谢。实际上，到目前为止所有的答案都非常有用。 - user3904868

Answer 2

在您的示例中，您可以将6个外部应用程序合并为3个，并将执行时间缩短一些。

SELECT * --Not real Select, set as * to simplify

FROM (SELECT t1.* FROM  (SELECT cleanFields1.* FROM Control AS cleanFields1 
WHERE cleanFields1.[QDate] = '2018-01-01') AS t1) t1 

OUTER APPLY (
    SELECT  COUNT(*) ProductCountMonth,
            COUNT(CASE WHEN t2.Issue = 1 THEN 1 END) ProductMatchMonth
    FROM    Control t2
    WHERE   t2.ProductName = t1.ProductName
    AND     t2.[QDate] < t1.[QDate]
    AND     MONTH(t2.[QDate]) = MONTH(t1.[QDate])

) t2

OUTER APPLY (
    SELECT  COUNT(*) ProductCountArea,
            COUNT(CASE WHEN t3.Issue = 1 THEN 1 END) ProductMatchArea
    FROM    Control t3
    WHERE   t3.ProductName = t1.ProductName
    AND     t3.[QDate] < t1.[QDate]
    AND     t3.AreaName = t1.AreaName

) t3

OUTER APPLY (
    SELECT  COUNT(*) ProductCountPType,
            COUNT(CASE WHEN t4.Issue = 1 THEN 1 END) ProductMatchPType
    FROM    Control t4
    WHERE   t4.ProductName = t1.ProductName
    AND     t4.[QDate] < t1.[QDate]
    AND     t4.PType = t1.PType

) t4

这个在你的匹配计数中使用了一个case表达式来确定Issue是否等于1。

- JamieD77

Answer 3

我建议你将outer apply改为窗口函数，这样会更快。但是你的SQL版本必须至少是2012年。

DECLARE @Date datetime;
SET @Date = '2018-01-01';

select
    *
from (
    SELECT 
        cleanFields1.*
        , row_number() over (partition by ProductName, month(QDate) order by QDate) - 1 AS ProductCountMonth
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, month(QDate) order by QDate) AS ProductMatchMonth
        , row_number() over (partition by ProductName, AreaName order by QDate) - 1 AS ProductCountArea
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, AreaName order by QDate) AS ProductMatchArea
        , row_number() over (partition by ProductName, PType order by QDate) - 1 AS ProductCountPType
        , sum(iif(Issue = 1, 1, 0)) over (partition by ProductName, PType order by QDate) AS ProductMatchPType
    FROM 
        Control AS cleanFields1
) t
where
    QDate = @Date

- uzi