14

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
1
  • Can you get rid of the "TOP 1"? I would think that is causing some overhead that may not be required if you are just checking to see if there is a single case...
    – JHFB
    Mar 28, 2012 at 18:06

5 Answers 5

11

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.

6
  • Okay, this speeds it up tremendously, however there's the potential for cascading trigger execution. If I use the same temp table names (#i, #d) in each trigger, they conflict. Is there a better/safer solution than just using a different temp table name in every trigger?
    – db2
    Mar 28, 2012 at 19:16
  • Could evaluate using table variables (with a primary key defined on CUSTNMBR to create the unique clustered index) and use the OPTION (RECOMPILE) hint to get it to take account of number of rows or maybe just use a particular naming convention such as #i_dbo_YourTable Mar 28, 2012 at 19:23
  • I think I'll settle for naming them like #trigger_name_i. If I go with table variables, I'll have to clutter up the code even more with explicit CREATE TABLEs. We've got cascading triggers, but not recursive triggers, so I think I'll be safe...
    – db2
    Mar 28, 2012 at 19:43
  • I recommend a table variable instead of a temp table for this purpose; table variables can still have primary and secondary (unique) indexes, they are automatically cleaned up when the trigger exits and table variables are scoped to just that trigger execution (it won't conflict with other table variables of the same name higher or lower on the call stack). To save on table definition code overhead, define a table type for each and use the type name to declare the table variables. Jan 29, 2019 at 18:41
  • 1
    @ChrisSmith you would often also need OPTION (RECOMPILE) so the cardinality is taken into account. Jan 29, 2019 at 19:11
10

I know this has been answered but it just popped up as recently active and I have run into this as well for tables with many millions of rows. While not discounting the accepted answer, I can at least add that my experience shows that a key factor in Trigger performance when doing similar tests (seeing if one or more columns have actually had their values changed) is whether or not the column(s) being tested were actually part of the UPDATE statement. I found that comparing columns between the inserted and deleted tables that were in fact not part of the UPDATE statement put a huge drag on performance that was otherwise not there if those fields were part of the UPDATE statement (regardless of their value actually being changed). Why do all of that work (i.e. a query to compare N fields across X rows) to determine if anything has changed if you can logically rule out the possibility of any of those columns being changed, which is obviously not possible if they were not present in the SET clause of the UPDATE statement.

The solution that I employed was to use the UPDATE() function which only works inside of Triggers. This built-in function tells you if a column was specified in the UPDATE statement and can be used to exit the Trigger if the columns that you are concerned about are not part of the UPDATE. This can be used in conjunction with a SELECT to determine if those columns, assuming that they are present in the UPDATE, have actual changes. I have code at the top of several audit triggers that looks like:

-- exit on updates that do not update the only 3 columns we ETL
IF (
     EXISTS(SELECT 1 FROM DELETED) -- this is an UPDATE (Trigger is AFTER INSERT, UPDATE)
     AND (
            NOT (UPDATE(Column3) OR UPDATE(Column7)
                 OR UPDATE(Column11)) -- the columns we care about are not being updated
            OR NOT EXISTS(
                        SELECT 1
                        FROM INSERTED ins
                        INNER JOIN DELETED del
                                ON del.KeyField1 = ins.KeyField1
                                AND del.KeyField2 = ins.KeyField2
                        WHERE ins.Column3 <> del.Column3
                                 COLLATE Latin1_General_100_CS_AS -- case-sensitive compare
                        OR    ISNULL(ins.Column7, -99) <> 
                                 ISNULL(del.Column7, -99) -- NULLable INT field
                        OR    ins.[Column11] <> del.[Column11] -- NOT NULL INT field
                      )
          )
    )
BEGIN
    RETURN;
END;

This logic will proceed to the rest of the trigger if:

  1. The operation is an INSERT
  2. At least one of the relevant fields is in the SET clause of an UPDATE and at least one of those columns in one row has changed

The NOT (UPDATE...) OR NOT EXISTS() might look odd or backwards, but it is designed to avoid doing the SELECT on the inserted and deleted tables if none of the relevant columns are part of the UPDATE.

Depending on your needs, the COLUMNS_UPDATED() function is another option to determine which columns are part of the UPDATE statement.

9
  • 1
    Good point that they should check UPDATE(CUSTCLAS) and just skip the whole thing if false (+1). I don't think you are correct that non updated columns aren't as readily available in the row versions as updated ones though. Dec 19, 2013 at 15:44
  • @MartinSmith, how do we go about proving it one way or the other? Although, it might not matter if the behavior is predictable in the manner that I have found. I just know that it is a drastic performance difference doing the same SELECT, JOINing between INSERTED and DELETED, checking fields for actual differences, depending on if the fields in the WHERE were in the SET of the UPDATE or not. The behavior I have seen is consistent, hence my theory, but it would be good/interesting to know the real reason. I suspected that fields not in the SET had to go back to the base table for their value. Dec 19, 2013 at 15:59
  • I have looked at the structure of this before. I can't remember if I found a good way of doing it or I just used an easily find able string and an exhaustive search through tempdb with DBCC PAGE Dec 19, 2013 at 16:08
  • OK. On an instance with a minimally sized single file tempdb I just tried this script, pasted the output into notepad and searched for "EEEEEE". I see the output in the screenshot here. Note before and after versions of both columns in both rows. There may be much easier ways but sufficient for my purposes here! Dec 19, 2013 at 17:31
  • Though actually there are other long EEEEEE strings in the tempdb pages not next to BBBBBB or DDDDDD. Might have to do some more investigation! Though maybe this is due to the REPLICATE call. Dec 19, 2013 at 17:41
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
1

http://dave.brittens.org/blog/writing-well-behaved-triggers.html

According to Dave, you should use temp tables or table variables with indexes, because the virtual INSERTED/DELETED tables have none. If you have the possibility of recursive triggers, then you should use table variables to avoid name collisions.

Hope someone finds this helpful as the original post was quite some time ago...

-1

The following code might increase the performance of this trigger. I did not know the correct data type of the [custclass] column so you need to adjust it.

DECLARE @i AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
DECLARE @d AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
INSERT INTO @i SELECT CUSTNMBR, custclass FROM inserted
INSERT INTO @d SELECT CUSTNMBR, custclass FROM deleted
IF NOT EXISTS
  (SELECT * FROM @i AS i INNER JOIN @d AS d ON d.CUSTNMBR = i.CUSTNMBR
   WHERE i.custclass <> d.custclass) RETURN

Notice that you can include additional columns in these in memory copies of the inserted and deleted tables if you need them in your trigger code. The primary keys on these tables will greatly increase join performance when updating more than a few rows at once. Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.