主页 / dba / 问题 / 162174
Accepted
Pரதீப்
Asked: 2017-01-26 03:30:08 +0800 CST

外部应用与左连接与派生表中的聚合

  • 772

考虑以下设置。涉及三个表#CCP_DETAILS_TEMP，Period并且ACTUALS_DETAILS

#CCP_DETAILS_TEMPwill have 50000records, ACTUALS_DETAILScan have 5000000records and periodtable will have 2000records

索引详情：

CREATE UNIQUE CLUSTERED INDEX IX_CCP_DETAILS_TEMP
  ON #CCP_DETAILS_TEMP (CCP_DETAILS_SID)

CREATE NONCLUSTERED INDEX IXN_ACTUALS_DETAILS_PERIOD_SID_RS_MODEL_SID_CCP_DETAILS_SID_QUANTITY_INCLUSION
  ON ACTUALS_DETAILS (PERIOD_SID, CCP_DETAILS_SID, RS_MODEL_SID, QUANTITY_INCLUSION)
  INCLUDE( SALES, QUANTITY, DISCOUNT) 

CREATE UNIQUE CLUSTERED INDEX IX_PERIOD
  ON PERIOD (PERIOD_SID)

我有一个要求，为此我编写了三种不同的方法来实现结果。现在我想知道哪个更好。

所有三个查询都或多或少地同时运行。我需要一些专家的建议，哪些会表现更好。任何方法都有什么缺点吗

方法一： Outer Apply

用的时间： 4615 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624)A
       OUTER apply (SELECT Sum(SALES),
                           Sum(QUANTITY)
                    FROM   [DBO].[ACTUALS_DETAILS] ad
                    WHERE  a.PERIOD_SID = ad.PERIOD_SID
                           AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
                           AND QUANTITY_INCLUSION = 'Y') oa (sales, quantity)

在此处输入图像描述

查询统计：

表“期间”。扫描计数 1，逻辑读取 2，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“#CCP_DETAILS_TEMP”。扫描计数 16，逻辑读取 688，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 16，逻辑读取 807232，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“ACTUALS_DETAILS”。扫描计数 1200000，逻辑读取 3859053，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间：CPU 时间 = 36796 毫秒，经过时间 = 4615 毫秒。

SQL Server 执行时间：CPU 时间 = 0 毫秒，经过时间 = 0 毫秒。

方法二： Left Join

用的时间： 4293 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       Sum(SALES),
       Sum(QUANTITY)
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN [ACTUALS_DETAILS] ad
              ON a.PERIOD_SID = ad.PERIOD_SID
                 AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID
                 AND QUANTITY_INCLUSION = 'Y'
GROUP  BY c.CCP_DETAILS_SID,
          A.PERIOD_SID

在此处输入图像描述

查询统计：

表“ACTUALS_DETAILS”。扫描计数 17，逻辑读取 37134，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“期间”。扫描计数 1，逻辑读取 2，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“#CCP_DETAILS_TEMP”。扫描计数 16，逻辑读取 688，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 16，逻辑读取 807232，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作文件”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间：CPU 时间 = 7983 毫秒，经过时间 = 4293 毫秒。

SQL Server 执行时间：CPU 时间 = 0 毫秒，经过时间 = 0 毫秒。

方法 3：聚合 first 和 Left join：

用的时间： 4200 Milli Seconds

SELECT c.CCP_DETAILS_SID,
       A.PERIOD_SID,
       SALES,
       QUANTITY
FROM   #CCP_DETAILS_TEMP c
       CROSS JOIN (SELECT PERIOD_SID
                   FROM   BPIGTN_GAL_APP_DEV_ARM..PERIOD
                   WHERE  PERIOD_SID BETWEEN 577 AND 624) a
       LEFT JOIN (SELECT CCP_DETAILS_SID,
                         PERIOD_SID,
                         Sum(SALES)    SALES,
                         Sum(QUANTITY) QUANTITY
                  FROM   [ACTUALS_DETAILS] ad
                  WHERE  QUANTITY_INCLUSION = 'Y'
                  GROUP  BY CCP_DETAILS_SID,
                            PERIOD_SID) ad
              ON a.PERIOD_SID = ad.PERIOD_SID
                 AND ad.CCP_DETAILS_SID = c.CCP_DETAILS_SID

在此处输入图像描述

查询统计：

表“ACTUALS_DETAILS”。扫描计数 17，逻辑读取 37134，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 16，逻辑读取 807232，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作文件”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“期间”。扫描计数 1，逻辑读取 2，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“#CCP_DETAILS_TEMP”。扫描计数 16，逻辑读取 688，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

SQL Server 执行时间：CPU 时间 = 7731 毫秒，经过时间 = 4200 毫秒。

SQL Server 执行时间：CPU 时间 = 0 毫秒，经过时间 = 0 毫秒。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    对于未来的问题，请使用 粘贴计划发布实际的执行计划。我想我能够使用屏幕截图和您的STATISTICS输出对所有相关细节进行逆向工程，但我可能做错了一些事情。看起来您的计划正在以 16 的 DOP 运行，大约 50000 行从 中返回#CCP_DETAILS_TEMP，24 行从 中返回PERIOD。

    在所有三个查询计划中， 和 之间的连接#CCP_DETAILS_TEMP以PERIOD相同的方式执行，具有相同的STATISTICS输出，并用作连接到 的外部表ACTUALS_DETAILS。看起来 SQL Server 正在为该连接做正确的事情，但它并不那么有趣，所以我将跳过这部分。与你的比较无关。

    相关的是ACTUALS_DETAILS. 所有三个查询都在覆盖索引上使用索引搜索，但索引搜索的执行方式不同。在第一个查询中，使用PERIOD_SID和CCP_DETAILS_SID列执行了 1200000 次查找。在第二个和第三个查询中，使用 just 执行了 17 次搜索PERIOD_SID。我相信所有的行都是用 获取的PERIOD_SID BETWEEN 577 AND 624，所以索引搜索可以有效地被认为是一个并行的索引扫描，它以 开始PERIOD_SID = 577和结束PERIOD_SID = 624。这导致查询之间的 IO 存在很大差异：

    表“ACTUALS_DETAILS”。扫描计数 1200000，逻辑读取 3859053，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    表“ACTUALS_DETAILS”。扫描计数 17，逻辑读取 37134，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    不一遍又一遍地阅读相同的页面有很大的好处。虽然从技术上讲，伪扫描方法确实可以读取不需要的页面，但总体上执行的 IO 会少得多。我也认为 IO 差异直接导致第一个查询和其他两个查询之间 CPU 时间的巨大差异：36796 毫秒 vs 7731 毫秒。当第一个查询运行时，它平均保持 9 个 CPU 完全忙碌，而第二个和第三个查询只有不到 2 个忙碌的 CPU。这对于第一个查询来说是一个很大的缺点，您会在繁忙的系统上或者您的查询被迫以较低的 DOP 运行时注意到它。在我有限的经验中APPLY我注意到 SQL Server 查询优化器倾向于将其实现为带有索引搜索的嵌套循环连接。这应该被视为轶事证据，我相信也有例外，但它解释了你在这里看到的内容。

    查询 2 和 3 将连接实现ACTUALS_DETAILS为哈希连接。GROUP BY我假设将其推入派生表背后的想法ad是，SQL Server 将尽早执行聚合，您将加入更少的行并聚合更少的行。但是，SQL Server 重写了您的第二个查询以提前执行聚合。您可以判断，因为流聚合和哈希匹配运算符位于第二个计划中哈希匹配（右外连接）运算符的右侧。据我所知，第二个和第三个查询计划实际上是相同的，尽管第三个计划确实有一些额外的 0% 成本运算符。

    就我个人而言，我不会认为 4293 和 4200 毫秒的经过时间或 7983 和 7731 毫秒的 CPU 时间之间的差异具有统计学意义。如果您多次运行查询，则第二个查询可能比第三个查询快。我会使用对您来说更自然的任何一种查询方式。就个人而言，我会使用第三个查询，因为它更好地代表了我希望优化器执行的操作，即尽早执行聚合。

    • 5

相关问题