Why did this trigger change speed my query up?

Question

Hi,

I've been trying to solve a slow trigger problem and now that I have through trial and error, I still don't know what the original problem was.

The query I'm running is the following:

UPDATE tblA 
SET X = NULL
WHERE X IS NOT NULL AND Z = 0

It updates around 30k rows.

And the part of the AFTER INSERT, UPDATE trigger on tblA causing the problem was this:

IF EXISTS(SELECT 1
          FROM inserted
          LEFT JOIN deleted ON deleted.PK = inserted.PK
          WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
              OR inserted.Y <> deleted.Y
BEGIN

    -- The above condition is not met for my query so we would never get here
    INSERT INTO tblB
    (...)
    SELECT
    inserted.X,
    ...
    FROM
    inserted
    LEFT JOIN deleted ON deleted.PK = inserted.PK
    WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
        OR inserted.Y <> deleted.Y

END

I believe the above IF EXISTS was included to stop potential looping INSERT triggers from firing when no inserts actually happened, but that isn't actually a problem for tblB as it only has one trigger.

So I changed it to this:

INSERT INTO tblB
(...)
SELECT
inserted.X,
...
FROM
inserted
LEFT JOIN deleted ON deleted.PK = inserted.PK
WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL)
    OR inserted.Y <> deleted.Y

And the update query time has now gone down from > 1 hour to around 30 seconds.

I expected it to take exactly the same amount of time. Why is it faster?

UPDATE: After examining execution plan for running my update query with the slow trigger

The IF EXISTS check had a cost of 0%, with 73% of the cost going to another trigger's statement which inserts changes into an audit table. This doesn't seem unreasonable in itself as that statement is quite complex with lots of joins, but I am none the wiser as to why my change to rewrite the IF EXISTS has made any difference. Perhaps my IF EXISTS check is interfering with the audit table insertions somehow to slow them down, but I don't know why the new version doesn't do the same thing as it contains the same SELECT. [Most of this cost was going to an eager table spool.]

Another 13% of query cost is spent on a third trigger which updates the timestamp on tblA if particular column values have changed. This again joins on inserted and deleted, plus on tblA. This update statement would have had no effect for my query as column X changes are not worthy of updating the timestamp. [This cost was split between a hash match inner join between tblA and inserted, and a clustered index update - seems reasonable.]

To add more confusion: if I disable the trigger that cost 73% of the time but leave the old trigger mentioned above in place without my changes, my query still takes many hours to run. I haven't tried disabling the timestamp trigger.

Looking at the query plan when using the fast trigger, the ratios are almost exactly the same, but the overall time is just less.

Answer 1

well, i'm not really sure what happened between the two, but i can offer you a couple of tips to speed it up more

the first thing i would change is this:

WHERE (inserted.Y IS NOT NULL AND deleted.Y IS NULL) 

to this:

WHERE (inserted.Y >'' AND deleted.Y IS NULL) 

IS NULL causes an index seek, where as >'' allows sql do to a seek and giving you the same result set (depending on if y is an int, if it's a varchar then you might change to >='')

Answer 2

Hi,

Please investigate the execution plan and see what are the differences between each runs. I guess SQL-server uses a different execution plan for your exists(...) query than for insert-select as it doesn't have to reach for all the columns in the first case. If there are confusing indexes or confusing statistics, optimization may get confused and pick a really bad plan. For this reason, after you investigate and save execution plans, try to reorganize/rebuild all indexes and recompute statistics on that table.

Regards, Rob