主页 / dba / 问题 / 171030
Accepted
John Eisbrener
Asked: 2017-04-15 10:09:47 +0800 CST

使用窗口函数优化子查询

  • 772

由于我的性能调优技能似乎永远不够用，我总是想知道是否可以针对某些查询执行更多优化。这个问题涉及的情况是嵌套在子查询中的 Windowed MAX 函数。

我正在挖掘的数据是各种大型集合组上的一系列事务。我有 4 个重要字段，交易的唯一 ID，一批交易的组 ID，以及与相应的唯一交易或交易组相关的日期。大多数情况下，组日期与批次的最大唯一交易日期匹配，但有时会通过我们的系统进行手动调整，并且在捕获组交易日期后会发生唯一日期操作。此手动编辑不会按设计调整组日期。

我在此查询中确定的是唯一日期在组日期之后的那些记录。以下示例查询构建了我的场景的大致等效项，并且 SELECT 语句返回我正在寻找的记录，但是，我是否以最有效的方式接近此解决方案？在我的事实表加载过程中，这需要一段时间才能运行，因为我的记录计数高 9 位数字，但主要是我对子查询的蔑视让我想知道这里是否有更好的方法。我并不担心任何索引，因为我相信这些索引已经到位；我正在寻找的是一种替代查询方法，它可以实现相同的目标，但效率更高。欢迎任何反馈。

CREATE TABLE #Example
(
    UniqueID INT IDENTITY(1,1)
  , GroupID INT
  , GroupDate DATETIME
  , UniqueDate DATETIME
)

CREATE CLUSTERED INDEX [CX_1] ON [#Example]
(
    [UniqueID] ASC
)


SET NOCOUNT ON

--Populate some test data
DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME

WHILE @i < 10000
BEGIN

    IF((@i + @j)%173 = 0)
    BEGIN
        SET @UniqueDate = GETDATE()+@i+5
    END
    ELSE
    BEGIN
        SET @UniqueDate = GETDATE()+@i
    END

    SET @GroupDate = GETDATE()+(@j-1)

    INSERT INTO #Example (GroupID, GroupDate, UniqueDate)
    VALUES (@j, @GroupDate, @UniqueDate)

    SET @i = @i + 1

    IF (@i % 5 = 0)
    BEGIN
        SET @j = @j+5
    END
END
SET NOCOUNT OFF

CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example]
(
    [GroupID] ASC,
    [UniqueDate] ASC,
    [GroupDate] ASC
)
INCLUDE ([UniqueID])

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT UniqueID
     , GroupID
     , GroupDate
     , UniqueDate
FROM (
    SELECT UniqueID
         , GroupID
         , GroupDate
         , UniqueDate
         , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate
    FROM #Example
    ) calc_maxUD
WHERE maxUniqueDate > GroupDate
    AND maxUniqueDate = UniqueDate

DROP TABLE #Example

dbfiddle在这里

sql-server sql-server-2012

5 个回答

  1. 当您能够从 SQL Server 2012 升级到 SQL Server 2016 时，您可能能够利用新的批处理模式 Window Aggregate 运算符提供的性能大大提高（尤其是对于无框架窗口聚合）​​。

    几乎所有的大型数据处理方案都使用列存储存储比使用行存储更好。即使不为您的基表更改为列存储，您仍然可以通过在其中一个基表上创建一个空的非聚集列存储过滤索引或通过冗余外部连接到一个列存储组织来获得新的 2016 运算符和批处理模式执行的好处桌子。

    使用第二个选项，查询变为：

    -- Just to get batch mode processing and the window aggregate operator
CREATE TABLE #Dummy (a integer NOT NULL, INDEX DummyCC CLUSTERED COLUMNSTORE);

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT
    calc_maxUD.UniqueID,
    calc_maxUD.GroupID,
    calc_maxUD.GroupDate,
    calc_maxUD.UniqueDate
FROM 
(
    SELECT
        E.UniqueID,
        E.GroupID,
        E.GroupDate,
        E.UniqueDate,
        maxUniqueDate = MAX(UniqueDate) OVER (
            PARTITION BY GroupID)
    FROM #Example AS E
    LEFT JOIN #Dummy AS D -- The only change to the original query
        ON 1 = 0
) AS calc_maxUD
WHERE 
    calc_maxUD.maxUniqueDate > calc_maxUD.GroupDate
    AND calc_maxUD.maxUniqueDate = calc_maxUD.UniqueDate;

    db<>小提琴

    请注意，对原始查询的唯一更改是创建一个空的临时表并添加左连接。执行计划是：

    批处理模式窗口聚合计划

    (58 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table '#Example'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0

    有关更多信息和选项，请参阅 Itzik Ben-Gan 的优秀系列，关于 SQL Server 2016 中的批处理模式窗口聚合运算符您需要了解的内容（分三部分）。

    • 11
  2. Best Answer

    我假设没有索引，因为您没有提供任何索引。

    马上，以下索引将消除计划中的排序运算符，否则可能会消耗大量内存：

    CREATE INDEX IX ON #Example (GroupID, UniqueDate) INCLUDE (UniqueID, GroupDate);

    在这种情况下，子查询不是性能问题。如果有的话，我会考虑消除窗口函数（MAX...OVER）以避免嵌套循环和表假脱机构造的方法。

    使用相同的索引，下面的查询乍一看可能效率较低，它确实对基表进行了 2 到 3 次扫描，但由于缺少 Spool 操作符，它在内部消除了大量读取。我猜它的性能仍然会更好，特别是如果您的服务器上有足够的 CPU 内核和 IO 性能：

    SELECT e.UniqueID
     , e.GroupID
     , e.GroupDate
     , e.UniqueDate
FROM (
    SELECT GroupID, MAX(UniqueDate) AS maxUniqueDate
    FROM #Example
    GROUP BY GroupID) AS agg
INNER JOIN #Example AS e ON agg.GroupID=e.GroupID
WHERE agg.maxUniqueDate > e.GroupDate
    AND agg.maxUniqueDate = e.UniqueDate
OPTION (MERGE JOIN);

    （注意：我添加了一个MERGE JOIN查询提示，但如果您的统计信息有序，这可能会自动发生。最佳做法是尽可能留下这些提示。）

    • 9

  3. 我只是要把 ol' Cross Apply 扔出去：

    SELECT e.*
    FROM #Example AS e
    CROSS APPLY ( SELECT TOP 1 e2.UniqueDate AS maxUniqueDate
                    FROM #Example AS e2
                    WHERE e2.GroupID = e.GroupID 
                    ORDER BY e2.UniqueDate DESC
                    ) AS ca
    WHERE ca.maxUniqueDate > e.GroupDate
        AND ca.maxUniqueDate = e.UniqueDate;

    有一些有点什么索引，它做得很好。

    CREATE CLUSTERED INDEX cx_whatever ON #Example (GroupID)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #Example (GroupID, UniqueDate DESC, GroupDate)

    统计时间和 io 看起来像这样（您的查询是第一个结果）

    Table 'Worktable'. Scan count 3, logical reads 28004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 1, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 20 ms.

Table '#Example'. Scan count 10001, logical reads 21336, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 11 ms.

    查询计划在这里（同样，您的计划是第一个）：

    https://www.brentozar.com/pastetheplan/?id=BJYJvqAal

    为什么我更喜欢这个版本？我避开线轴。如果这些开始溢出到磁盘，它会变得丑陋。

    但你可能也想试试这个。

    SELECT e.*
    FROM #Example AS e
    CROSS APPLY ( SELECT e2.UniqueDate AS maxUniqueDate
                    FROM #Example AS e2
                    WHERE e2.GroupID = e.GroupID 
                    ) AS ca
    WHERE ca.maxUniqueDate > e.GroupDate
        AND ca.maxUniqueDate = e.UniqueDate;

    如果这是一个大型 DW，您可能更喜欢 Hash Join，并且在 join 中进行行过滤，而不是在TOP 1查询末尾作为 Filter 运算符。

    计划在这里：https ://www.brentozar.com/pastetheplan/?id=BkUF55ATx

    在这里统计时间和 io：

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 2, logical reads 84, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 5 ms.

    希望这可以帮助！

    一次编辑，基于@ypercube 的想法和一个新索引。

    CREATE NONCLUSTERED INDEX ix_meh ON #Example (UniqueDate,GroupDate) INCLUDE (UniqueID,GroupID);

WITH t1 AS 
(
    SELECT DISTINCT
    e.GroupID ,
    MAX(UniqueDate) AS MaxUniqueDate
    FROM #Example AS e
    GROUP BY e.GroupID
)
SELECT *
FROM #Example AS e
CROSS APPLY (
SELECT *
FROM t1
    WHERE t1.MaxUniqueDate > e.GroupDate
        AND t1.MaxUniqueDate = e.UniqueDate
        AND t1.GroupID = e.GroupID
) ca

    这是统计时间和 io：

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 2, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

    这是计划：

    https://www.brentozar.com/pastetheplan/?id=SJv8foR6g

    • 8

  4. 我会看看top with ties

    如果GroupDate是相同的GroupId那么：

    select top 1 with ties 
   UniqueID
 , GroupID
 , GroupDate
 , UniqueDate
from #Example
where UniqueDate > GroupDate
order by row_number() over (partition by GroupId order by UniqueDate desc)

    其他：top with ties在公用表表达式中使用

    with cte as (
  select top 1 with ties 
      UniqueID
    , GroupID
    , GroupDate
    , UniqueDate
  from #Example
  order by row_number() over (partition by GroupId order by UniqueDate desc)
)
select *
from cte
where UniqueDate > GroupDate

    dbfiddle：http ://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c058994c2f5f3d99b212f06e1dae9fd3

    原始查询

    Table 'Worktable'. Scan count 3, logical reads 28001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 31 ms.

    vstop with ties在公用表表达式中

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 15 ms.
    • 4

  5. 因此，我对迄今为止发布的各种方法进行了一些分析，在我的环境中，看起来 Daniel 的方法在执行时间上始终胜出。令人惊讶的是（对我来说）sp_BlitzErik 的第三个 CROSS APPLY 方法并没有落后太多。如果有人感兴趣，这是输出，但感谢 TON 提供的所有替代方法。我从挖掘这个问题的答案中学到的东西比我在很长一段时间内学到的更多！

    Windowed Function - baseline metric

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 9, logical reads 140181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89815, logical reads 42553550, physical reads 0, read-ahead reads 84586, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 9, logical reads 7819, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 87753 ms,  elapsed time = 13031 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


Basic Aggregated Subquery - Daniel Hutmacher

(10406 row(s) affected)
Table 'DateDim'. Scan count 18, logical reads 1194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 18, logical reads 280362, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 48, logical reads 82408, physical reads 9629, read-ahead reads 72779, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6861425, physical reads 0, read-ahead reads 14565, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 18, logical reads 15726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 40527 ms,  elapsed time = 6182 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


CROSS APPLY Operation A - sp_BlitzErik

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 6199331, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 3099273, logical reads 12844012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 3109676, logical reads 9350502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 3109676, logical reads 9482456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 132632 ms,  elapsed time = 20955 ms.


CROSS APPLY Operation C - sp_BlitzErik

(10406 row(s) affected)
Table 'DateDim'. Scan count 18, logical reads 1194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 18, logical reads 280362, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 56, logical reads 92800, physical reads 10872, read-ahead reads 81928, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6861425, physical reads 0, read-ahead reads 14563, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 18, logical reads 15376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 18, logical reads 15726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 46082 ms,  elapsed time = 6804 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


TOP 1 WITH TIES - B - SqlZim

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 9, logical reads 140181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6866304, physical reads 0, read-ahead reads 93468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 9, logical reads 7835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 79406 ms,  elapsed time = 15852 ms.
    • 4

相关问题