CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user10285265
user10285265

Reputation:

CTE's being called multiple times?

I have been tasked with looking into a very slow SQL query. If my understanding of CTE's is correct, they are called every time the CTE is being called from the outer query.

WITH 

UserAccount 
    AS
        (
            SELECT uaa.AccountId FROM PaymentInitiation.UserAccountAccess uaa
            WHERE uaa.UserId = @userId
        ),
        
CTE_TransactionId (TransactionId)
    AS
        (
            SELECT TOP 100 TransactionId 
            FROM   PaymentInitiation.TransactionMetaDataDebit T 
            JOIN   UserAccount UA ON T.AccountId = UA.AccountId  
            WHERE   T.TransactionId < @seekPoint ORDER BY T.TransactionId DESC
        ) 
        
SELECT 
    PT.Id,
    InstructionId,
    ContractReference,
    CpgReference,
    ModuleTypeId,
    ProductType,
    PT.ModifiedAt,
    VirtualAccountId,
    CounterpartyExchangeRate 
FROM PaymentInitiation.PaymentTransactions PT 
JOIN CTE_TransactionId P ON PT.Id = P.TransactionId

This query causes an execution plan with nested loops and a whole lot of number of executions.

enter image description here

enter image description here

So my question is really - is it the CTEs that are causing the query to choke because it is executed on earch row in the set ?

Or am I looking at this wrong ?

Thanks a lot.

Upvotes: 0

Views: 152

Answers (1)

SteveC
SteveC

Reputation: 6015

I think you're looking to end up with something like this

with CTE_TransactionId (TransactionId) AS (
    SELECT TOP 100 TransactionId 
    FROM   PaymentInitiation.UserAccountAccess uaa 
            join PaymentInitiation.TransactionMetaDataDebit T on uaa.AccountId=T.AccountId
    WHERE  UAA.UserId=@userId 
            and T.TransactionId < @seekPoint 
    ORDER BY T.TransactionId DESC) 
SELECT 
    PT.Id,
    InstructionId,
    ContractReference,
    CpgReference,
    ModuleTypeId,
    ProductType,
    PT.ModifiedAt,
    VirtualAccountId,
    CounterpartyExchangeRate 
FROM PaymentInitiation.PaymentTransactions PT 
     join CTE_TransactionId P ON PT.Id = P.TransactionId;

Upvotes: 0

Related Questions