January 6, 2009 at 2:13 pm
By the way if the columns are "nullable" you need three way comparison like:
(
i.col1 <> d.col1
or (i.col1 is null and d.col1 is not null)
or (i.col1 is not null and d.col1 is null)
)
PER column 😉
* Noel
January 6, 2009 at 2:21 pm
noeld (1/6/2009)
By the way if the columns are "nullable" you need three way comparison like:(
i.col1 <> d.col1
or (i.col1 is null and d.col1 is not null)
or (i.col1 is not null and d.col1 is null)
)
PER column 😉
Good to know, thanks
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 2:29 pm
Marios Philippopoulos (1/6/2009)
GSquared (1/6/2009)
On the point of making sure the inserted and deleted tables are different, a trigger can look like this:
update MyTable
set LastUpdate = getdate(), LastUpdateBy = user
from dbo.MyTable
inner join inserted
on MyTable.ID = inserted.ID
inner join deleted
on MyTable.ID = deleted.ID
and (inserted.FirstName != deleted.FirstName
or
inserted.LastName != deleted.LastName)
In the second part of the join to deleted, you include all the columns you are auditing, and if LastName is updated from 'Squared' to 'Squared', with no other change, then it won't get included in the update command from the trigger.
Won't this SQL statement become very expensive if there are, say, 20 potential columns being updated in the table?
I'm focusing on the last part of the SQL where the inserted and deleted table columns are being compared.
Won't there be a tipping point at which using the trigger may be preferable? Thinking out loud here...
This is a trigger-only structure, since it uses the inserted and deleted columns in the Where clause, so that's not the issue here.
Yes, if you have a lot of columns, this kind of Where clause can slow things down a bit. It also has drawbacks if you add a column to a table and forget to add it to the trigger. Plus, all those Or statements can result in table scans, which can kill performance, especially if you update a lot of rows at once.
Whether you use this or not depends on how important it is that you only audit updates that actually change anything. On the other hand, you might need to know if someone did an update that didn't change anything. Depends on what your audit is for.
I'm not recommending using this. I was just answering what it does.
I use something like this in my auditing triggers, but I do it to save audit table space, not to restrict actions logged. What I use isn't an "Or" construct, so it doesn't have the performance drawback. And it alerts me if a column is added or removed that needs to be changed in the trigger. Similar concept, completely different implementation.
Personally, I try to build triggers, especially audit triggers, to have the least possible performance footprint in order to get their job done. Assuming I have to use any at all, that is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 2:33 pm
Just had another thought. You could accomplish much the same thing as the complex join statement by using an Except query between inserted and deleted and then joining to that recordset.
;with ChangedRows as
(select *
from inserted
except
select *
from deleted)
update MyTable
set ... -- Audit columns here
from dbo.MyTable
inner join ChangedRows
on MyTable.ID = ChangedRows.ID
That would avoid the "added or removed a column" problem, as well as all the Or statements in the Where clause. Haven't tested it for performance, but it might work quite well. Again, it's going to depend a lot on how many rows you update all at once.
Also, of course, the decision to use it or not should be based on the purpose of the audit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 2:36 pm
GSquared (1/6/2009)[hr
This is a trigger-only structure, since it uses the inserted and deleted columns in the Where clause, so that's not the issue here.
Mea culpa, I don't know what I was thinking, of course this SQL was only to be used within the trigger.
Thank you for the other points you made as well.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 2:37 pm
GSquared (1/6/2009)
Just had another thought. You could accomplish much the same thing as the complex join statement by using an Except query between inserted and deleted and then joining to that recordset.
;with ChangedRows as
(select *
from inserted
except
select *
from deleted)
update MyTable
set ... -- Audit columns here
from dbo.MyTable
inner join ChangedRows
on MyTable.ID = ChangedRows.ID
That would avoid the "added or removed a column" problem, as well as all the Or statements in the Where clause. Haven't tested it for performance, but it might work quite well. Again, it's going to depend a lot on how many rows you update all at once.
Also, of course, the decision to use it or not should be based on the purpose of the audit.
Hmmm interesting... I like the except construct!
* Noel
January 6, 2009 at 2:38 pm
GSquared (1/6/2009)
Just had another thought. You could accomplish much the same thing as the complex join statement by using an Except query between inserted and deleted and then joining to that recordset.
;with ChangedRows as
(select *
from inserted
except
select *
from deleted)
update MyTable
set ... -- Audit columns here
from dbo.MyTable
inner join ChangedRows
on MyTable.ID = ChangedRows.ID
That would avoid the "added or removed a column" problem, as well as all the Or statements in the Where clause. Haven't tested it for performance, but it might work quite well. Again, it's going to depend a lot on how many rows you update all at once.
Also, of course, the decision to use it or not should be based on the purpose of the audit.
Neat, can't wait to try this...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 2:41 pm
To clarify, the decision as to whether to include it in a trigger or not is based on coding needs, not on performance.
If the table will ever be updated through anything that isn't a standardized proc, you need it to be a trigger. That means if a proc will ever be written by anyone who doesn't understand the audit policy, it needs to be a trigger.
If the updates will only ever be through standardized procs, you can put the code in there to avoid the potential double-row-version issue. Or leave it in the trigger. Either one (not both, of course).
But it's purely a question of policy enforcement, not a question of performance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 2:52 pm
GSquared (1/6/2009)
To clarify, the decision as to whether to include it in a trigger or not is based on coding needs, not on performance.If the table will ever be updated through anything that isn't a standardized proc, you need it to be a trigger. That means if a proc will ever be written by anyone who doesn't understand the audit policy, it needs to be a trigger.
If the updates will only ever be through standardized procs, you can put the code in there to avoid the potential double-row-version issue. Or leave it in the trigger. Either one (not both, of course).
But it's purely a question of policy enforcement, not a question of performance.
But the way the trigger is written will have performance implications, will it not? Here by performance I also include utilization of tempdb resources, which was my initial concern with this. Not using the trigger, even though it may be the result of a policy or standard, results in not expending these tempdb resources. So policy and resource use are linked.
Also, the latest version of the trigger that you posted may be faster than the one in my OP for a given table. It's for sure sexier...;)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 3:08 pm
The way the trigger is written will definitely have performance implications. The question of whether or not to write a trigger is based on policy. How to write it, if you need it, is another question entirely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 3:30 pm
Having seen your post that lead to this one, is your real problem with the performance or in the space taken in tempdb? If it is just space taken then you should be able to reduce it by breaking down the original insert into multiple batches. If I remember correctly the versioned records are removed when they are no longer needed - in your case when the trigger is done with them.
January 6, 2009 at 3:41 pm
matt stockham (1/6/2009)
Having seen your post that lead to this one, is your real problem with the performance or in the space taken in tempdb? If it is just space taken then you should be able to reduce it by breaking down the original insert into multiple batches. If I remember correctly the versioned records are removed when they are no longer needed - in your case when the trigger is done with them.
Yes, my main concern is with tempdb utilization, as about 3 GB of (version-store) tempdb space is taken up by this single transaction.
Performance, per se, is a secondary issue here (in the sense that it did not trigger my original enquiry).
Unfortunately, the business logic would not allow for this to be broken into multiple batches.
This is not a catastrophic issue for us; we can absorb a few extra GB of tempdb use. It was mainly a matter of curiosity for me to track down the culprit of version-store usage, my first one... 🙂
In terms of performance, the current trigger performs about 20,000,000 logical reads (on a 2,000,000 row update of the underlying table), so, yes, it would be nice if that could be reduced by re-writing the trigger in a more optimal way.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 6, 2009 at 4:29 pm
Don't forget to post back with the results ... 😉
* Noel
January 7, 2009 at 4:04 am
noeld (1/6/2009)
Don't forget to post back with the results ... 😉
Will do.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 8, 2009 at 9:07 am
It is my belief that even if you rewrite the trigger to only do the audit update on rows where something is different between inserted and deleted the trigger will still have to hit 2+M rows in the base table to gather the comparison data thus 20M IOs and 3GB of tempdb space will still be used, even if it winds up that you are only truly modifying 1 row of actual data. That doesn't even make much sense to me at the moment since why affect 2M+ rows if you only modify 1?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply