Speeding up the Execution of Triggers.
-
Hello,
I have a trigger defined for a permanent table..
This trigger Updates more than 3000 records.
Every time i do a update to the table I get a time out error because of the Trigger.
Every help is welcome and would be thankfull
-Sharan
-
Could you post the trigger so we can have a look at it?
Pretty hard to guess what's wrong with it, other than it's probably 'bad' in one way or another...
/Kenneth
-
My guess is you're doing too much work in the trigger. Trigger code should be tight and do minimal work.
-
My guess... Trigger is causing excessive locking!
Details on the implementation are needed so that we can help you
* Noel -
Thanks..
The Trigger Updates Table2 based on the modification done to the 8 columns in Table1 for which the Trigger is defined.
If Update(Table1.Column1)
Update Table2
Set Table2.Column1 = @data
Where (Based on certain Criteria)
Similar Kind of statements has been defined for Other 7 columns.
How to determine the Excessive Lockings caused by the Triggers?
-Sharan
-
Thanks..
The Trigger Updates Table2 based on the modification done to the 8 columns in Table1 for which the Trigger is defined.
If Update(Table1.Column1)
Update Table2
Set Table2.Column1 = @data
Where (Based on certain Criteria)
Similar Kind of statements has been defined for Other 7 columns.
How to determine the Excessive Lockings caused by the Triggers?
-Sharan
-
This is what I call "RBAR" (pronounced "ree-bar" and is a "Modenism" for "Row by Agonizing Row") because you have variables on the right side of an update. The only way we're going to be able to give you a good recommendation on this is if you post the code for the whole trigger so we can help you turn it into some high speed set based code. 3000 rows of update should take well less than a second
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A quick Question, wat are the different ways to increase the Connection and Command Timeout Properties of the sql connection string?
-Sharan
-
The default is like 30 seconds. It's already 30 times bigger than it needs to be. So I'd suggest you improve the performance of the code instead of the length of the time out.
-
You can extend the command timeout via a simple property if you're using either ADO or ADO.Net, but as noted it's not a good idea - unless! - its more of a back office type job that just needs to run and you have bigger fish to fry. Hopefully you can make it faster with better indexing, query, etc, but at some point you might wind up in the same place again if the number of records gets larger. The only permanent fix is to just queue something to be done in the trigger, then act on it async via a job or other process. That might be just dumping the entire deleted (or inserted) table into a holding table, or just grabbing the id's of the modified rows.
-
Andy, Can you explain it in detail on what you wanted to say.
Thanks
-Sharan
-
Create WorkQueueTable (permanent table)
(
id int
)
then in the trigger, you simply insert the ids in that table
Then you assign a job to run each X minutes or whatever delay you feel is right, and then in that job you run the long queries. But to be perfectly honest I never needed to do that, ever. I always found a better way to improve my statements' performance beyond that point of no return. That's why I'd recommend AGAIN that you do that FIRST.
-
Your trigger must look like this:
UPDATE T2
SET Column1 = i.ColumnA
FROM inserted i -- system table containing rows from Table1 affected by update/insert
INNER JOIN Table2 T2 ON {join criteria between tables Table1(represented by inserted here) and Table2}
Where (Based on certain Criteria)
That's it.
One statement. No loops, no cursors.
And if you need to check if any of 8 columns updated read about COLUMNS_UPDATED ( ) in CREATE TRIGGER topic in BOL.
_____________
Code for TallyGenerator -
I'd recommend that changing the timeout is a patch, not a fix. All you'd end up doing is allowing code that desparatly needs to be fixed to take its sweet time running. I'll say it again... Post the code and let us see what we can do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
-Sharan
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply