September 19, 2008 at 8:25 am
I'm coming from an Oracle background to SQL Server 2005. One huge difference I'm seeing so far is that if I have an update statement that updates 10 rows the trigger on that table will only fire once (in Oracle it would fire 10 times if I had a row level trigger).
I have a trigger setup that uses the Primary Key of the inserted/updated row and goes and populates a 2nd table. Because the trigger only fires once even if I update 10 rows I created a cursor within the trigger to go through each row inserted/updated.
During performance testing we found that we were getting deadlocks with a lot users and it seems to be inside this trigger. I came across this post [/url] which suggests using a stored procedure and in the trigger create a big (in oracle terms) execute immediate statement. In Oracle using execute immediate is exceptionally slow and you wouldn't even think about using a solution like the one described in the link.
Bottom line: is it really better to do an execute immediate than to have a cursor within a trigger? Or, even better yet, is there a way I can get a SQL Server 2005 trigger to fire for each row inserted/updated?
Thanks!
September 19, 2008 at 9:21 am
MS SQL Server does not perform well doing row-by-row operations. By putting the cursor within your trigger, you have essentially serialized any set-based operation you could have had. You really want to avoid this.
This is one of the areas in which Oracle is really forgiving - it is basically always processing on a row-by-row level and can handle it pretty well. I think that's why a lot of developers like it also, it follows the Action/Loop/Repeat programming they are used to.
If you can post the code for your trigger, and possibly some DDL for the tables, you are pretty likely to get some help re-writing this in a more MS SQL friendly manner. If you really want to get a fast response, here are some tips for posting code:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
September 19, 2008 at 9:24 am
SQL triggers fire once per operation, not once per row. SQL doesn't have row-triggers.
Cursors in a trigger are a near-guarantee of poor performance and potential deadlocks. Triggers should be written to operate on a set, not on individual rows. I wouldn't recommend a stored procedure, as often that mean a cursor.
Can you post the trigger as you have it currently (with the cursor) and we can suggest a more optimal way to write for SQL Server.
I have a trigger setup that uses the Primary Key of the inserted/updated row and goes and populates a 2nd table. Because the trigger only fires once even if I update 10 rows I created a cursor within the trigger to go through each row inserted/updated.
For something like that, I would write a trigger like this (rough, but should give you the idea)
CREATE TRIGGER trg_SomeTable_Insert ON SomeTable
AFTER INSERT
AS
INSERT INTO SomeOtherTable (Col1, Col2, Col3)
SELECT col1, col2, col3 FROM inserted -- pseudo table that contains the inserted rows
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply