Louis Hillebrand (8/25/2014)
Chris,Here is the execution plan..
The OUTER APPLY does 100k seeks on the Follow-Up table.
Louis.
The cost is as likely to be the residual predicate on the quotation table. Try this small mod:
SELECT@F = COALESCE(@From, Min(QuotationDate)),
@T = COALESCE(@To , Max(QuotationDate))
FROMdbo.Quotation;
SELECT QU.QuotationId,
ISNULL(QUIC.Next_Follow_Up, 0)
FROM dbo.Quotation QU
OUTER APPLY (
SELECT TOP 1 Next_Follow_Up
FROM Quotation.Follow_Up f
WHERE f.Quotation_Id = QU.QuotationId
ORDER BY Id DESC
) QUIC
WHERE (QU.QuotationDate > @F OR QUIC.Next_Follow_Up > 0)
AND QU.QuotationDate <= @T
ORDER BY QU.QuotationId DESC;
WITH QU_Memo(Quotation_Id, R, Next_Follow_Up) AS (
SELECT Quotation_Id,
ROW_NUMBER() OVER (PARTITION BY Quotation_Id ORDER BY Id DESC),
Next_Follow_Up
FROM Quotation.Follow_Up
)
SELECT QU.QuotationId,
COALESCE(QUIC.Next_Follow_Up, 0)
FROM dbo.Quotation QU
LEFT OUTER JOIN (SELECT * FROM QU_Memo WHERE R = 1) QUIC ON QU.QuotationId = QUIC.Quotation_Id
--WHERE (QU.QuotationDate > @From OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )
--AND QU.QuotationDate <= @To
WHERE (QU.QuotationDate > @F OR COALESCE(QUIC.Next_Follow_Up, 0) > 0 )
AND QU.QuotationDate <= @T
ORDER BY QU.QuotationId DESC;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden