SQL Server Execution Plan Review Request

Question

Having trouble understanding why my query is taking so long, looking for advice to optimise please.

update Laserbeak_Main.dbo.ACCOUNT_MPN set
    DateUpgrade = ord.ConnectedDate
                    FROM [ORDER] ord
                    WHERE ord.AccountNumber = Laserbeak_Main.dbo.ACCOUNT_MPN.AccountNumber
                    AND ord.ordertypeID = '2'
                    AND ord.ConnectedDate IS NOT NULL
                    AND DateUpgrade <> ord.ConnectedDate

Execution plan as requested on brentozar.com

UPDATE: Following suggestions the new query looks like this & seems to work much more quickly. However if you run the query it sets the rows as expected, then run again it updates the exact same number of rows. Converting to a select confirms that the same rows are being updated each time. The <> clause should stop this but it doesn't. I believed it was something to do with collation but have been unable to confirm if its possible to have different collations at table level in the same database.

;WITH cteOrderInfo AS (
SELECT DISTINCT ord.AccountNumber, ord.ConnectedDate
FROM [ORDER] ord
WHERE ord.ordertypeID = '2'
AND ord.ConnectedDate IS NOT NULL
)
UPDATE Laserbeak_Main.dbo.ACCOUNT_MPN
SET Laserbeak_Main.dbo.ACCOUNT_MPN.DateUpgrade = cteOrderInfo.ConnectedDate
FROM cteOrderInfo
INNER JOIN Laserbeak_Main.dbo.ACCOUNT_MPN acc
ON cteOrderInfo.AccountNumber = acc.AccountNumber
WHERE cteOrderInfo.ConnectedDate <> acc.DateUpgrade

The SELECT to confirm:

;WITH cteOrderInfo AS (
SELECT DISTINCT ord.AccountNumber, ord.ConnectedDate
FROM [ORDER] ord
WHERE ord.ordertypeID = '2'
AND ord.ConnectedDate IS NOT NULL
)
SELECT cteOrderInfo.ConnectedDate, acc.DateUpgrade
FROM cteOrderInfo
INNER JOIN Laserbeak_Main.dbo.ACCOUNT_MPN acc
ON cteOrderInfo.AccountNumber = acc.AccountNumber
WHERE cteOrderInfo.ConnectedDate <> acc.DateUpgrade

SELECT Results Sample:

Answer 1

As Serge suggested, we did not have unique rows.

the solution we arrived at:

;WITH cteSourceStuff AS (
      SELECT AccountNumber, MpnUpgrade, MAX(DateConnected) maxConnDate
      FROM ORDER_DETAIL, [ORDER]
      WHERE ORDER_DETAIL.OrderID = [ORDER].OrderID
      AND LEN(MpnUpgrade) > 10
      AND OrderTypeID = 2
      GROUP BY AccountNumber, MpnUpgrade
      )
      UPDATE Laserbeak_Main.dbo.ACCOUNT_MPN set
              DateUpgrade = cteSourceStuff.maxConnDate
              FROM cteSourceStuff
              WHERE cteSourceStuff.MpnUpgrade = ACCOUNT_MPN.Mpn
              AND cteSourceStuff.AccountNumber = ACCOUNT_MPN.AccountNumber
              AND DateUpgrade <> cteSourceStuff.maxConnDate

This works because the duplicates are initially removed, then we only update the rows that we are actually targeting. The reason we have issues before was that SQL was updating the 1st row it found, then when we re-ran or ran the select it was return rows matched on the key but that had not previously been updated.