为什么这个CTE比使用临时表慢得多？ [英] Why is this CTE so much slower than using temp tables?

问题描述

自从我们的数据库最近更新以来，我们遇到了一个问题（我做了此更新，在此感到内），此后使用的查询速度慢得多。我试图修改查询以获得更快的结果，并设法通过临时表实现了目标，这还不错，但是我无法理解为什么该解决方案比基于CTE的性能更好一个，它执行相同的查询。也许必须要做的是某些表位于不同的数据库中？

We had an issue since a recent update on our database (I made this update, I am guilty here), one of the query used was much slower since then. I tried to modify the query to get faster result, and managed to achieve my goal with temp tables, which is not bad, but I fail to understand why this solution performs better than a CTE based one, which does the same queries. Maybe it has to do that some tables are in a different DB ?

这是执行不好的查询（在我们的硬件上为22分钟）：

Here's the query that performs badly (22 minutes on our hardware) :

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分钟）

When running both CTE queries separately, it's super fast (0 secs in SSMS, returns 122 rows and 13k rows), when running the full query, with INNER JOIN on sEmail, it's super slow (22 minutes)

这里的查询性能很好，使用临时表（在我们的硬件上为0秒），并且执行相同的操作，返回的结果相同：

Here's the query that performs well, with temp tables (0 sec on our hardware) and which does the eaxct same thing, returns the same result :

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行

• OtherDb.dbo.Purchased_Email_List : 13 rows, 2 rows flagged bPattern = 1
• OtherDb.dbo.Purchased_Email_List_Email : 324289 rows, 122 rows with patterns (which are used in this issue)
• dbo.NewsletterService_import_list_email : 15.5M rows
• dbo.NewsletterService_import_list_email_distinct ~1.5M rows
• WHERE ILE.iId_newsletterservice_import_list = 1000 retrieves ~ 13k rows

I可以根据要求发布有关表的更多信息。

I can post more info about tables on request.

有人可以帮我理解吗？

更新

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

Here is the query plan for the CTE query :

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

Here is the query plan with temp tables :

推荐答案

如您在查询计划中所见，对于CTE，引擎保留以下权利：基本上将它们用作查找，即使您想要加入时也是如此。

As you can see in the query plan, with CTEs, the engine reserves the right to apply them basically as a lookup, even when you want a join.

如果不确定它是否可以预先独立运行整个事情，本质上会生成一个临时表...让我们为每行运行一次。

If it isn't sure enough it can run the whole thing independently, in advance, essentially generating a temp table... let's just run it once for each row.

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

This is perfect for the recursion queries they can do like magic.

但是，您看到的是-在嵌套的嵌套循环中，它可能会发生严重错误。

您已经在自己的设备上找到了答案通过尝试实际的临时表来拥有。

But you're seeing - in the nested Nested Loops - where it can go terribly wrong.
You're already finding the answer on your own by trying the real temp table.

这篇关于为什么这个CTE比使用临时表慢得多？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！