Lazy Spool causes the slow query
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.
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:
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;
2016-01-18 04:27:43
Wow…the improvement is great. Thank you for your help!!