• 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;

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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