多外部应用变慢，提高查询速度的最佳方法

I'm trying to do lots of in table data collation where single record needs to collate data based on the same table but with previous records by date.

我正在尝试在表数据排序中做大量的工作，其中单个记录需要基于相同的表进行数据排序，但是要按照日期对以前的记录进行排序。

Currently I have 6 OUTER APPLY which is taking approx 3 minutes to run for 1 date. I will probably need 50+ calculated fields and multiple dates so this is beginning to look unworkable.

目前我有6个外部申请，大约需要3分钟的时间来进行1次约会。我可能需要50多个计算字段和多个日期，所以这看起来不太可行。

Is there a better way to do this to improve query speed?

有更好的方法来提高查询速度吗?

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

EDIT:

编辑:

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

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

Desired Output:

期望的输出:

3 个解决方案

#1

You can eliminate all those cross applys which will greatly help performance. Also you should avoid things like '1' when Issue is an int. You should use 1.

您可以消除所有这些交叉应用程序，这将极大地帮助性能。当问题是int类型时，你应该避免使用“1”这样的词。

In this case I used a cte to show how you can isolate the rows you want returned. From that is just some conditional aggregation.

在本例中，我使用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

#2

I suggest you to change your outer apply's to window functions. Must be faster. But your SQL version must be at least 2012

我建议你改变你的外部应用到窗口的功能。必须得更快。但是您的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

#3

In your example, you can combine the 6 outer apply's into 3 and cut the execution time down some.

在您的示例中，您可以将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

This uses a case expression in your Match counts to determine if Issue = 1.

这使用匹配计数中的case表达式来确定问题是否为1。

#1