CODEWITHSUNDEEP
sqlsql-servermsdncross-apply
TS-
TS-

Reputation: 369

SQL Cross Apply with three tables

I am trying to combine 3 tables using Cross Apply in a time-efficient manor. I can get the results that I want, but the run time is too great. The three tables are:

-CUSTOMERS, which has the columns CustomerId(primary key) and CurrentSetType

-HISTORY, which has the columns CustomerId(foreign key), SetType, and TimeStamp

-UPDATELIST, which has the column CustomerId

My goal is to find the most recent SetType from HISTORY for each CustomerId in UPDATELIST that is different from the CurrentSetType (this is part of a glorified 'undo' button). I believe my problem is that the CUSTOMERS and HISTORY tables are enormous, and I don't think I'm getting them paired down to the smaller UPDATELIST before doing a cross apply on the entire thing. My current query is this:

DECLARE @UPDATELIST TABLE (Identifier INT NOT NULL PRIMARY KEY);
INSERT INTO @UPDATELIST (Identifier) VALUES (#####); -- a few hundred lines of this

SELECT CustomerId, ITEM.SetType
FROM CUSTOMERS
CROSS APPLY
 (SELECT TOP 1 SetType FROM HISTORY
  WHERE HISTORY.CustomerId IN (SELECT Identifier FROM @UPDATELIST)
  AND HISTORY.CustomerId = CUSTOMERS.CustomerId
  AND HISTORY.SetType != CUSTOMERS.CurrentSetType ORDER BY TimeStamp DESC) AS ITEM

What is the most efficient query for this?

EDIT: I am using MSDN SQL version 12.0.5532

Upvotes: 2

Views: 1631

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

My first thought would be something like this:

SELECT
    CustomerID
    , SetType
FROM
    (
        SELECT
            C.CustomerID
            , H.SetType
            , ROW_NUMBER() OVER (PARTITION BY C.CustomerID ORDER BY H.TimeStamp DESC) R
        FROM
            CUSTOMERS C
            JOIN UPDATELIST U ON U.CustomerId = C.CustomerId
            JOIN HISTORY H ON
                H.CustomerId = C.CustomerId
                AND H.SetType <> C.CurrentSetType
    ) Q
WHERE R = 1

How does that work?

Upvotes: 1

Related Questions