主页 / dba / 问题 / 81405
Accepted
James Anderson
Asked: 2014-10-30 09:30:33 +0800 CST

加入前过滤表

  • 772

我有一个用 Id 列表填充临时表 #employee_benefits 的存储过程。该表最终大约有 10,000 行长。然后下面的查询从一个名为 EmployeeBenefitData 的表中进行选择，该表有大约 400 万行。

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId

瓶颈是对 EmployeeBenefitData 表的索引扫描。它首先进行索引扫描，然后将其加入临时表。临时表充当过滤器，这意味着在连接之前扫描所有数据效率非常低。我添加了以下代码以将扫描更改为搜索并显着减少所需的读取量。

DECLARE @MinEmpBenId INT, @MaxEmpBenId INT

SELECT @MinEmpBenId = MIN(EmployeeBenefitId), @MaxEmpBenId = MAX(EmployeeBenefitId)
FROM #employee_benefits

SELECT  ebd.EmployeeBenefitDataId, ebd.EmployeeBenefitId, ebd.[DataDefinitionId],
        dd.TypeId, dd.DataDefinitionId, dd.Name, ebd.[Value], ebd.[Date], ebd.[Text]
FROM    #employee_benefits eb
INNER JOIN EmployeeBenefitData ebd ON eb.EmployeeBenefitId = ebd.EmployeeBenefitId
INNER JOIN DataDefinition dd ON ebd.DataDefinitionId = dd.DataDefinitionId
WHERE   ebd.EmployeeBenefitId >= @MinEmpBenId AND ebd.EmployeeBenefitId <= @MaxEmpBenId

它对客户统计数据产生了巨大影响

总执行时间 74, 1794
服务器回复的等待时间 11, 11

我的问题是：这是好的做法吗？为什么优化器不这样做呢？

更新 我应该提到临时表在 EmployeeBenefitID 上有一个聚集索引

sql-server sql-server-2012

2 个回答

  1. Best Answer

    这是好习惯吗？

    在这种情况下，我会说是的。我可能还会添加一个OPTION (RECOMPILE)让它“嗅探”变量值。最佳计划可能会根据较大表中与此范围匹配的行的比例而有所不同。

    它为优化器提供了一个潜在有用的额外路径，据我所知，它不是查询优化器自己做过的事情。最接近它的是，使用合并连接，当任一输入完成时，它将停止处理输入。因此，这意味着它可能会避免完全扫描。

    唯一想到的缺点是最小/最大范围值的计算本身可能很昂贵（但如果您用作过滤器的表在该列上被索引，这应该非常便宜）。

    我创建了两个测试表

    CREATE TABLE EmployeeBenefitData(EmployeeID INT PRIMARY KEY);

CREATE TABLE FilteredEmployee(EmployeeID INT PRIMARY KEY);

    并使用 1 到 4,000,000（6,456 页）的整数加载 EmployeeBenefitData

    以及具有 2,000,000 和 2,010,000 的整数的 FilteredEmployee（19 页）

    然后运行以下形式的 6 个查询

    DECLARE @E1 INT,
        @E2 INT

SELECT @E1 = FE.EmployeeID,
       @E2 = EBD.EmployeeID
FROM   FilteredEmployee FE
       INNER LOOP JOIN EmployeeBenefitData EBD
         ON FE.EmployeeID = EBD.EmployeeID
OPTION (MAXDOP 1);

    通过反转两个表的顺序并尝试所有三种连接类型LOOP, MERGE,来组成 6 个排列HASH。

    结果如下

    +------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |      6456 |             0 |  8250009 |
| FE         | EBD         | Merge |              1 |      3257 |             1 |       19 |
| EBD        | FE          | Merge |              1 |      3257 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |      6456 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |      6456 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

    上图说明了关于合并连接的要点，因为它“仅”扫描了大表的一半以上。它仍然首先读取从 1 到 1,999,999 的所有行并丢弃它们。

    用 a 重复实验WHERE EBD.EmployeeID BETWEEN 2000000 AND 2010000得到以下结果。

    +------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |        21 |             0 |    20636 |
| FE         | EBD         | Merge |              1 |        21 |             1 |       19 |
| EBD        | FE          | Merge |              1 |        21 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |        21 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |        21 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

    唯一没有从附加范围谓词中受益的查询是较大表位于嵌套循环连接内部的查询。

    这当然不足为奇，因为该计划（下面的计划 1）是由使用来自 的值的重复索引搜索驱动的FilteredEmployee。

    在此处输入图像描述

    计划 1 也是优化器在没有范围谓词的情况下“自然”选择的计划。有了范围谓词，它选择了一个不同的合并连接计划来寻找相关的索引范围，而不扫描不必要的行，并且成本要低得多（计划 2）

    • 6

  2. 我已经看到查询计划器对无索引临时表执行类似的操作，即使有时临时表中的行数很少。

    尝试添加一个索引来覆盖您正在加入和过滤的列，以查看规划器是否使用这些统计信息来注意到它可以以更有效的方式实现目标。

    另外：如果您在所有要加入的表上都有适当的索引，您可以添加一个索引提示（WITH(INDEX(<index_name>))在 from 子句之后添加）以使查询计划器以某种方式运行，尽管这确实使您的视图/过程依赖于索引名称并强制他们查询规划器走特定路线，因为它可能会随着数据的增长/变化而选择更好的路线。

    • 0

相关问题