sql - 两个 CTE 的内部连接需要很长时间

Question

我正在使用生产数据库，我需要将我的资源重新安排到多个 CTE 中，以便获得与我的数据匹配的正确格式。

这会导致 CTE1 有大约 7000 条记录(需要几秒钟)和 CTE2 有大约 55000 条记录(需要不到一秒)。

下一步是根据两列连接两个 CTE。此步骤耗时 19 分钟，CTE2 仅限于 TOP 1000!由于这些是 CTE，因此没有单一索引。我的问题是我怎样才能在合适的时间内完成这场比赛(即使一两分钟就足够了)。

CTE1 由四个字母数字列组成，最多 8 个字符。CTE2 由三个字母数字列组成，最多 8 个字符和两个日期时间。inner join 匹配来自 CTE1 的 2 个字母数字列和来自 CTE2 的 2 个字母数字列。

示例 CTE1(7000 条记录)

A     | B     | C         | D
---------------------------------------------------------------------------
ABC   | 123   | 1234567   | 12345678

• A 列有两个不同的值，与 CTE2.A 中的不同值相匹配。
• B 列有大约 6500 个不同的列。并非所有值都在 CTE2.B 中表示。

示例 CTE2(55000 条记录)

A     | B     | C     | D                         | E
---------------------------------------------------------------------------
ABC   | 123   | XYZ   | 2013-10-11 15:00:00.000   | 2013-10-11 16:00:00.000

• A 列有两个不同的值，与 CTE1.A 中的不同值相匹配。
• B 列有大约 2000 个不同的列。并非所有值(尽管大多数)都在 CTE1.B 中表示。

查询(CTE2的TOP 1000用这个查询耗时19分钟)

SELECT     CTE1.C
           ,CTE1.D
           ,CTE2.B
           ,CTE2.C
           ,CTE2.D
           ,CTE2.E
FROM       CTE1
INNER JOIN CTE2 ON CTE1.A = CTE2.A
                 AND CTE1.B = CTE2.B

Answer 1

这是对 Gareth 的回答的扩展。

SQL Server 不会单独执行 CTE，然后将它们组合在最终查询中。这是一个已知问题，并且有一个查询提示请求会强制实现 CTE。您可以对请求进行投票here .

相反，它将 CTE 作为代码，将它们填充到查询中，然后“优化”整个过程。对于长而复杂的查询，它可能会出错。

影响性能的最常见错误是将连接更改为未定义索引的嵌套循环连接。您可以通过查看执行计划来了解是否发生这种情况。如果它包含嵌套循环，那么您可能会有麻烦。

如果是这样，请尝试使用 OPTION (HASH JOIN, MERGE JOIN) 运行查询以避免嵌套循环连接。