Atrocious performance joining INSERTED and DELETED tables in a trigger

Question

I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a single UPDATE statement.

The first thing the trigger does is check to see if any updated rows had the value of this column changed from the value in question. It simply joins INSERTED to DELETED and compares the value in that column. If nothing qualifies, it bails out early so the UPDATE statement doesn't run.

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

In this case, CUSTNMBR is the primary key of the underlying table. If I do a large update on this table (say, 5000+ rows), this statement takes AGES, even if I haven't touched the CUSTCLAS column. I can watch it stall on this statement for several minutes in Profiler.

The execution plan is bizarre. It shows an Inserted Scan with 3,714 executions, and ~18.5 million output rows. That runs through a filter on the CUSTCLAS column. It joins this (via nested loop) to a Deleted Scan (also filtered on CUSTCLAS), which executes only once and has 5000 output rows.

What idiotic thing am I doing here to cause this? Note that the trigger absolutely must properly handle multi-row updates.

EDIT:

I also tried writing it like this (in case EXISTS was doing something unpleasant), but it's still just as terrible.

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN

Answer 1

You could evaluate using explicit INNER MERGE JOIN or INNER HASH JOIN hints but given that you are presumably using these tables again later in the trigger you are probably better off just inserting the contents of inserted and deleted tables into indexed #temp tables and being done with it.

They do not get useful indexes created for them automatically.

Answer 2

I might try to rewrite using if exists

IF EXISTS (SELECT TOP 1 i.CUSTNMBR     
            FROM INSERTED i         
            INNER JOIN DELETED d             
            ON i.CUSTNMBR = d.CUSTNMBR and d.custclass = 'Misc'  
            WHERE d.CUSTCLAS <>i.CUSTCLAS)    
BEGIN

--do your triggerstuff here
END