sql-server - 为什么这个 CTE 比使用临时表慢这么多？

Question

自从最近更新我们的数据库(我做了这个更新，我在这里有罪)以来我们遇到了一个问题，从那时起使用的查询之一慢了很多。我尝试修改查询以获得更快的结果，并设法使用临时表实现了我的目标，这还不错，但我不明白为什么这个解决方案比基于 CTE 的解决方案执行得更好one，执行相同的查询。可能是因为某些表位于不同的数据库中？

这是执行不佳的查询(在我们的硬件上为 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 分钟)

这是执行良好的查询，使用临时表(在我们的硬件上为 0 秒)并且执行相同的操作，返回相同的结果:

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 查询的查询计划:

这是带有临时表的查询计划:

Answer 1

正如您在查询计划中看到的那样，对于 CTE，引擎保留将它们基本上用作查找的权利，即使您想要连接也是如此。

如果它不够确定，它可以提前独立运行整个过程，本质上是生成一个临时表……让我们为每一行运行一次。

这非常适合他们可以像魔术一样执行的递归查询。

但您会看到 - 在嵌套的嵌套循环中 - 它可能会出现严重错误。
您已经通过尝试真正的临时表自行找到了答案。