July 20, 2006 at 9:53 am
I am trying to tune a Delete trigger that is performing poorly. In the trigger there is an update statement that looks something like this (I have oversimplified it in this example):
update AnotherTable a
set a.Col = getdate()
from deleted d
where a.Col2 = d.Col2
My question is: Would the deleted table ever contain more than one row? If not would I gain any benefit from rewriting the update to look more like this:
update AnotherTable a
set a.Col = @getdate-2()
where a.Col2 = d.Col2
A "Deleted Scan" is being shown in the Actual Execution Plan for the first query, which lead me to this question - I am hoping to avoid this scan.
Any guidance would be greatly appreciated.
thanks!
Keith
keith
July 20, 2006 at 9:59 am
How do you mean performaing badly, what metrics are you using to make this assumption?
I would only ever expect a scan on deleted, if the table is small ( a couple of rows/pages ) then a scan would be most efficient.
Triggers are on my top ten ways to cripple performance for a database!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 20, 2006 at 10:10 am
How do you mean performaing badly, what metrics are you using to make this assumption?
I would only ever expect a scan on deleted, if the table is small ( a couple of rows/pages ) then a scan would be most efficient.
Triggers are on my top ten ways to cripple performance for a database!!
I am using very basic metrics. The most basic being that each delete from my base table is taking 9 seconds to complete. In looking at the execution plan, the cost of the update statement in the trigger is 92% relative to the batch.
You say "if the deleted table is small" - my question is in what case would the deleted (or updated, inserted) table in the trigger ever have more than one row? Isn't the role of these tables to mirror the record being deleted?
I agree with your assessment of triggers in general, however I am working with a 3rd party replication mechanism over which I have little control - so if I can't get rid of the trigger, I'd like it to at least perform better.
Keith
keith
July 20, 2006 at 1:02 pm
The deleted table can get more that one record!
it happens every time a batch ( with updates or deletes)that affects more than one record is executed.
Triggers should ALWAYS be coded to handle multiple rows!!!
9 seconds to delete a record ??? WOW.
make sure that AnotherTable.Col2 is indexed
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply