Does checking inside a trigger for column updates add any significant overhead? An employee was questioning the extra processing required to check the UPDATE(ColumnName) and I'm wondering what that actually costs in performance.
We have a set of triggers that we use to insert data into audit tables on UPDATE, the audit tables have all the same columns as the original tables plus a couple of info columns, one of them is a varchar(MAX) that stores any column name that was updated on an update. They were all built from the same script and have the same format of:
INSERT INTO audit.OriginalTableName_audit
SELECT [col1],[col2],...(etc),
CASE WHEN UPDATE([col1] THEN '[col1] - ' ELSE '' END +
CASE WHEN UPDATE([col1] THEN '[col1] - ' ELSE '' END + (etc).
FROM DELETED
Of course, for various values of "significant"! Kidding aside, what you are doing is fairly typical. Naturally there is a cost. But ask that employee, "What is the business cost of not doing the check?" I'll bet it is worse (probably much, much worse) than any possible performance hit.
To properly answer the question, you need to establish a baseline for performance of updates/inserts/deletes without the trigger, then measure performance with the trigger, then compare the two and determine if the difference is significant.
And therein lies the rub. Significant can mean different things to different people.
Before you do anything, get a clear definition of what it means in this context.
Well at the very least, the SELECT * FROM DELETED triggers are staying, we are required to have audit records so the trigger as a whole isn't going anywhere. It's just the CASE WHEN UPDATE([]) additional performance that's in question.
I'm working on rewriting the triggers and audit tables to not have that and test, I was just wondering if anyone knew or had experience with the additional builtin UPDATE function with triggers.
We have some procs that already have some timer/resource logging in them so should be easy to get a comparison with/out them.
The cost of doing the UPDATE(column_name) is trivial compared to the extremely huge overhead of concatenating all the values into a single varchar(max) column. You're focusing on a pennies of performance (non)issue and ignoring the billions of dollars of performance issue.
Not sure if you were replying to me @ScottPlecher? but just to clarify my earlier psot:
will store the column name and a trailing "-" for any column that was included in the INSERT/UPDATE statement. That's fine, but I've known folk to think that that indicates that the column actually changed, which is not necessarily the case (for an UPDATE)
A comparison of [inserted] and [deleted] values would provide a true indicator for whether the data in that column had changed, that's pretty trivial. I definitely wouldn't concatenate the values, and I ALSO would NOT do the "one row per changed column" that I see done sometimes as an audit. Horrendous to report on, miles of code in the trigger, and probably runs like a snail too ...
Test results:
Short answer is zero impact from a time perspective (which is all our end users care about).
Running a set up updates on 10,000 records doing 10 updates per record so I end up with 100,000 records in the audit table, each run takes approx 13 mins (big queries, slow disk). Ran it once with no triggers to get a baseline, and then ran it again with the trigger with the UPDATE([col]) , then ran with the triggers without the UPDATE field and then again with no triggers to check the baseline. Then did that all again two more times.
For 2 of the 3 test runs, it was actually faster to run the queries with the triggers than it was for the baseline, but all within 10-15 seconds of each other so no measurable impact in our case.