为什么使用CTE比使用临时表慢这么多？

Question

自最近一次我们数据库的更新以来，我们遇到了一个问题（我是做这个更新的人，我有罪），其中一个查询自那时起就变得慢了很多。 我试图修改查询以获得更快的结果，并设法通过临时表达到了目标，这不错，但我无法理解为什么这种解决方案比基于CTE的解决方案表现更好，这两种方案执行相同的查询。 也许是因为一些表位于不同的数据库中？ 这是执行效果差的查询（在我们的硬件上需要22分钟）：

WITH CTE_Patterns AS (
SELECT 
    PEL.iId_purchased_email_list,
    PELE.sEmail
FROM OtherDb.dbo.Purchased_Email_List PEL WITH(NOLOCK)
    INNER JOIN OtherDb.dbo.Purchased_Email_List_Email AS PELE WITH(NOLOCK) ON PELE.iId_purchased_email_list = PEL.iId_purchased_email_list
WHERE PEL.bPattern = 1
),
CTE_Emails AS (
    SELECT 
        ILE.iId_newsletterservice_import_list, 
        ILE.iId_newsletterservice_import_list_email, 
        ILED.sEmail
    FROM dbo.NewsletterService_import_list_email AS ILE WITH(NOLOCK)
        INNER JOIN dbo.NewsletterService_import_list_email_distinct AS ILED WITH(NOLOCK) ON ILED.iId_newsletterservice_import_list_email_distinct = ILE.iId_newsletterservice_import_list_email_distinct
    WHERE ILE.iId_newsletterservice_import_list = 1000
)
SELECT I.iId_newsletterservice_import_list, 
        I.iId_newsletterservice_import_list_email, 
        BL.iId_purchased_email_list
FROM CTE_Patterns AS BL WITH(NOLOCK)
    INNER JOIN CTE_Emails AS I WITH(NOLOCK) ON I.sEmail LIKE BL.sEmail

当分别运行CTE查询时，速度非常快（在SSMS中为0秒，返回122行和13k行），但在完整查询中使用sEmail的INNER JOIN时速度非常慢（22分钟）。

以下是执行良好、使用临时表并且完成相同操作、返回相同结果的查询：

SELECT
    PEL.iId_purchased_email_list,
    PELE.sEmail
INTO #tb1
FROM OtherDb.dbo.Purchased_Email_List PEL WITH(NOLOCK)
    INNER JOIN OtherDb.dbo.Purchased_Email_List_Email PELE ON PELE.iId_purchased_email_list = PEL.iId_purchased_email_list
WHERE PEL.bPattern = 1

SELECT 
    ILE.iId_newsletterservice_import_list, 
    ILE.iId_newsletterservice_import_list_email, 
    ILED.sEmail
INTO #tb2
FROM dbo.NewsletterService_import_list_email AS ILE WITH(NOLOCK)
    INNER JOIN dbo.NewsletterService_import_list_email_distinct AS ILED ON ILED.iId_newsletterservice_import_list_email_distinct = ILE.iId_newsletterservice_import_list_email_distinct
WHERE ILE.iId_newsletterservice_import_list = 1000

SELECT I.iId_newsletterservice_import_list, 
        I.iId_newsletterservice_import_list_email, 
        BL.iId_purchased_email_list
FROM #tb1 AS BL WITH(NOLOCK)
    INNER JOIN #tb2 AS I WITH(NOLOCK) ON I.sEmail LIKE BL.sEmail

DROP TABLE #tb1
DROP TABLE #tb2

表格统计信息：

• OtherDb.dbo.Purchased_Email_List：13行，其中2行标记为bPattern=1
• OtherDb.dbo.Purchased_Email_List_Email：324289行，其中122行包含模式（在此问题中使用）
• dbo.NewsletterService_import_list_email：1550万行
• dbo.NewsletterService_import_list_email_distinct：约150万行
• WHERE ILE.iId_newsletterservice_import_list = 1000检索到约13k行

如需更多关于表格的信息，请告知。

能否有人帮我理解这些内容？

更新

以下是CTE查询的查询计划：

以下是使用临时表的查询计划：

- MaxiWheat

---

@TimSchmelter 我理解你的观点，但是在类似这样的CTE情况下，查询优化器是否做得不太好呢？在我看来，它们应该像小型临时表一样运作，因为我在每个CTE中都包含一个WHERE子句，使得集合变得更小。 - MaxiWheat

有时候，堆叠的CTE太过复杂，会给优化器带来太多机会，导致它无法高效地达到最佳状态以满足整个查询。有时候过期的统计信息也会对此造成影响，从而导致做出错误选择。将中间结果转储到#temp表中可以帮助解决这个问题，因为它既可以（a）减少任何单个查询的复杂性，又可以（b）提供数据子集的真实和准确的统计信息。 - Aaron Bertrand

此外，不要在每个表上都放置 WITH (NOLOCK)，而是在会话级别上设置它 - 或者更好的方法是使用 RCSI。 - Aaron Bertrand

我会帮忙，但前提是你从数据库中删除我的电子邮件地址。 - swasheck

我知道这很旧了，但是还有其他人注意到CTE查询计划中的Hash匹配和表扫描吗？这表明查询需要索引。我敢打赌，如果使用正确的索引，这两个查询将执行相同。 - nflash

Answer 1

正如您在查询计划中看到的那样，使用公共表表达式（CTEs），引擎可以将其作为查找应用，即使您需要进行连接也是如此。如果不确定它是否能够独立运行整个操作，它会提前生成一个临时表，然后为每一行运行一次。这对于递归查询非常完美，就像魔法一样。但是，您可以在嵌套循环中发现问题。通过尝试真正的临时表，您已经自己找到了答案。

- Mike M

Answer 2

并行性。如果您在TEMP TABLE查询中注意到，第三个查询表示在分配和收集第一个查询的工作时存在并行性。当组合第一个和第二个查询的结果时也有并行性。第一个查询的相对成本为77%。因此，在您的TEMP TABLE示例中，查询引擎能够确定第一个查询可以受益于并行性。特别是当并行性是Gather Stream和Distribute Stream时，它允许分配工作（连接），因为数据以允许分配工作然后重新组合的方式分布。请注意，第二个查询的成本为0%，因此您可以忽略它，除非需要将其组合。

查看CTE时，它完全是按顺序处理的，而不是并行处理。因此，无论如何，使用CTE它都无法确定第一个查询可以并行运行，以及第一个和第二个查询之间的关系。可能是因为使用多个CTE表达式时，它假定存在某些依赖关系，并且没有足够地向前查找。

您可以使用CTE_Patterns但通过将CTE_Emails作为“子查询派生”表放置在CTE的第三个查询中来消除CTE。有趣的是，可以查看执行计划，并查看以这种方式表达时是否存在并行性。

- CodeCowboyOrg

Answer 3

根据我的经验，递归时最好使用CTE，需要与数据进行连接时使用临时表。通常可以使查询速度更快。

- SDr6