Home Forums SQL Server 2008 T-SQL (SS2K8) Improving query performance to detect first duplicate RE: Improving query performance to detect first duplicate

  • Sales 1 and 2 overlap on date range and have a common item & location.

    Same with 2 and 5. But 1 and 5 don't overlap: 1, 2 & 5 aren't a set with overlapping date ranges and location in common.

    It makes more sense to me to do this as sales pairs, as follows:

    SELECT

    s1_SaleID = s1.SaleID,

    s2_SaleID = s2.SaleID,

    l.*, i.*

    FROM Sale s1

    INNER JOIN Sale s2

    ON s1.SaleID < s2.SaleID

    AND S1.SaleStart <= S2.SaleEnd

    AND S2.SaleStart <= S1.SaleEnd

    CROSS APPLY (

    SELECT li.LocationID

    FROM SaleLocationXref li

    WHERE li.SaleID = s1.SaleID

    OR li.SaleID = s2.SaleID

    GROUP BY li.LocationID

    HAVING COUNT(*) = 2

    ) l

    CROSS APPLY (

    SELECT ii.ItemID

    FROM SaleItemXref ii

    WHERE ii.SaleID = s1.SaleID

    OR ii.SaleID = s2.SaleID

    GROUP BY ii.ItemID

    HAVING COUNT(*) = 2

    ) i

    “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