Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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 '12 at 18:06

4 Answers 4

up vote 6 down vote accepted

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.

share|improve this answer
    
But 5000 rows wouldn't use an index anyway most likely. –  HLGEM Mar 28 '12 at 18:38
    
@HLGEM - It would on the inside of a nested loops join as opposed to doing 5,000 table scans. Though maybe cardinality estimates are inaccurate as no column stats to work off and once in #temp table it won't use nested loops anyway... –  Martin Smith Mar 28 '12 at 18:42
    
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 '12 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 –  Martin Smith Mar 28 '12 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 '12 at 19:43

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 fields have actually had their values changed) is whether or not the field(s) being tested were actually part of the update. I found that comparing fields 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 fields 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 function tells you if a field 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 fields, assuming that they are present in the UPDATE, have real changes. I have code at the top of several audit triggers that looks like:

-- exit on updates that do not update the only 3 fields 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 fields 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 SQL_Latin1_General_CP1_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 fields 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 fields are part of the UPDATE.

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

share|improve this answer
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. –  Martin Smith Dec 19 '13 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. –  srutzky Dec 19 '13 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 –  Martin Smith Dec 19 '13 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! –  Martin Smith Dec 19 '13 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. –  Martin Smith Dec 19 '13 at 17:41

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!

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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