Why is this CTE so much slower than using temp tables?

Question

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 ?

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

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)

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

Tables stats :

• 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 can post more info about tables on request.

Can someone help me understand this ?

UPDATE

Here is the query plan for the CTE query :

Here is the query plan with temp tables :

Answer 1

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.