Lazy Spool causes the slow query

Kai 2016-01-06 02:27:18

I am trying to improve the performance of my query. I found the lazy spool operator is the top operation but have no idea where to start.
SQLkiwi 2016-01-06 10:40:09
Instead of the picture of the execution plan, attach the Plan Explorer session file – preferably created by running the query directly from Plan Explorer (Get Actual Plan). It would also be helpful to provide the DDL and indexes for the two tables involved.
SQLkiwi 2016-01-06 11:19:01
Subject to the clarifications requested, I would probably rewrite the query as:

UPDATE V
SET OpeningBalance = CA.CurrentBalance
FROM #v AS V
CROSS APPLY
(
    SELECT TOP (1)
        CLAL.CurrentBalance
    FROM dbo.CreditLimits AS CL
    JOIN dbo.CreditLimitAuditLog AS CLAL
        ON CLAL.CreditLimitId = CL.CreditLimitId
    WHERE
        CL.ClientECN = V.IssuedtoECN
        AND CL.CurrencyId = V.CurrencyId
        AND CL.IssuerId = V.IssuerId
        AND CLAL.ModifiedDate <= @ReportStartDate
    ORDER BY
        CLAL.ModifiedDate DESC
) AS CA;

With an index on dbo.CreditLimitAuditLog:

CREATE INDEX index_name 
ON dbo.CreditLimitAuditLog 
    (CreditLimitId, ModifiedDate, CurrentBalance);

Expected plan:

Plan

Notice the ORDER BY is different – it seems safer, and more plan-friendly, to sort on ModifiedDate DESC than CreditLimitAuditLogId DESC.

If (IssuedtoECN, CurrencyId, IssuerId) is a key for the temporary table, you should have this as a primary key or unique clustered index.

If you really must choose the CurrentBalance on the basis of CreditLimitAuditLogId DESC, you will probably need a FORCESEEK hint, though the resulting Top N Sort is unavoidable:

UPDATE V
SET OpeningBalance = CA.CurrentBalance
FROM #v AS V
CROSS APPLY
(
    SELECT TOP (1)
        CLAL.CurrentBalance
    FROM dbo.CreditLimits AS CL
    JOIN dbo.CreditLimitAuditLog AS CLAL WITH (FORCESEEK)
        ON CLAL.CreditLimitId = CL.CreditLimitId
    WHERE
        CL.ClientECN = V.IssuedtoECN
        AND CL.CurrencyId = V.CurrencyId
        AND CL.IssuerId = V.IssuerId
        AND CLAL.ModifiedDate <= @ReportStartDate
    ORDER BY
        CLAL.CreditLimitAuditLogId DESC
) AS CA;

Sort Plan

Kai 2016-01-18 04:27:43
Wow…the improvement is great. Thank you for your help!!
January 6, 2016
No Comments