March 1, 2012 at 3:31 am
The below observation is more a comment than a question, but I invite any remarks.
As a newcomer to SQL and studying hard to get going I've found SQL Central to be an excellent resource. Recently I saw an article regarding Triggers as opposed to a MERGE solution to a problem of tracking changes to a table.
I was intrigued by the solution using a trigger, as it seemed less code and more elegant etc.
So, I decided to implement the trigger example with a minor change and associated it with a sample table I have which has several million rows. I then tested the trigger by running an update which changed approximately 2.7 million rows. What has really surprised me is just how much slower the update runs with a trigger ..... much, much slower in fact.
Example, the above 2.7 million row update with the trigger enabled took 1 hour 36 minutes to complete.
With the trigger disabled, the same run took a mere 39 seconds!
If my calculations are correct, then this is around 147 times faster than the same update with a trigger.
I was amazed to say the least by the huge performance differential.
Comment invited.
Regards
Steve
March 1, 2012 at 3:53 am
I've yet to find a use for triggers if i'm honest (there will be some use for them in the future im sure)
If i'm not mistaken (someone will correct me if i'm wrong i'm sure) - When a trigger is fired they are evaluated for each row, that would be why it took so much longer. On 2.7 million rows not exactly an elegant set based solution 🙂
March 1, 2012 at 3:54 am
Triggers do add some overhead into processing and it's not good idea to put a lot of business logic into triggers.
However, there are some cases where triggers are winners and therefor you will probably find them in many databases. Implementing lightweight audit, balances re-calc, enforcement of some data integrity rules and many other...
The key thing in writing T-SQL for triggers (as in many other things in IT) is: do it properly!
Could you please post the code you have placed into trigger?
March 1, 2012 at 4:00 am
Loundy (3/1/2012)
I've yet to find a use for triggers if i'm honest (there will be some use for them in the future im sure)If i'm not mistaken (someone will correct me if i'm wrong i'm sure) - When a trigger is fired they are evaluated for each row, that would be why it took so much longer. On 2.7 million rows not exactly an elegant set based solution 🙂
1. You can start with this: http://searchsqlserver.techtarget.com/feature/Why-use-triggers-in-Microsoft-SQL-Server
2. You are mistaken! Triggers are fired for the operation not for each row!
I have triggers working with much larger sets of data just fine, you need to understand how they work and implement them right...
March 1, 2012 at 4:04 am
Eugene Elutin (3/1/2012)
Loundy (3/1/2012)
I've yet to find a use for triggers if i'm honest (there will be some use for them in the future im sure)If i'm not mistaken (someone will correct me if i'm wrong i'm sure) - When a trigger is fired they are evaluated for each row, that would be why it took so much longer. On 2.7 million rows not exactly an elegant set based solution 🙂
1. You can start with this: http://searchsqlserver.techtarget.com/feature/Why-use-triggers-in-Microsoft-SQL-Server
2. You are mistaken! Triggers are fired for the operation not for each row!
I have triggers working with much larger sets of data just fine, you need to understand how they work and implement them right...
Cool - was hoping someone would put me on the right path
March 1, 2012 at 4:27 am
There are many good uses for triggers and as many bad uses for triggers. The general rule for a trigger is to ensure it's as optimal as possible and does as little as possible, because it will be called once on every matching operation against the table
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
March 1, 2012 at 4:32 am
Of course ...
Here's the trigger code :
USE [SampleDB]
GO
/****** Object: Trigger [dbo].[TRG_Log_TempSales2_Changes] Script Date: 03/01/2012 11:17:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********************************************************************
* Function : Logs changes to the TempSales2 table into TempSales2_Changess_log
* Object Type : Table Trigger
* Language : Microsoft SQL Server 2008R2
* Database : 2012-02-29
* Author :
* Date Written :
*----------------------------------------------------------------------
* Modification History:
*
*******************************************8***************************/
ALTER TRIGGER [dbo].[TRG_Log_TempSales2_Changes]
ON [dbo].[TempSales2]
AFTERINSERT,
UPDATE,
DELETE
AS
SET NOCOUNT ON;
DECLARE @LogDateDATETIME= GETDATE(),-- Store date/time of logging
@ModeCHAR(1);-- Mode flag (I=Insert, U=Update,D=Delete)
-------------------- Determine mode
IF NOT EXISTS (SELECT 1 FROM DELETED)
SET @Mode = 'I'
ELSE
IF NOT EXISTS (SELECT 1 FROM INSERTED)
SET @Mode = 'D'
ELSE
SET @Mode = 'U';
-------------------- This is an insert or update
INSERT INTO TempSales2_Changes_Log
SELECTRecID,
@Mode,
@LogDate
FROMINSERTED;
-------------------- This is an insert or update
INSERT INTO TempSales2_Changes_Log
SELECTRecID,
@Mode,
@LogDate
FROMDELETED AS d
WHEREd.RecID NOT IN (SELECT i.RecID FROM INSERTED AS i);
March 1, 2012 at 4:38 am
Can you try this:
ALTER TRIGGER [dbo].[TRG_Log_TempSales2_Changes]
ON [dbo].[TempSales2]
AFTERINSERT,
UPDATE,
DELETE
AS
SET NOCOUNT ON;
-------------------- Determine mode
IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
IF EXISTS (SELECT 1 FROM DELETED)
INSERT INTO TempSales2_Changes_Log SELECTRecID, 'U', GETDATE() FROMINSERTED;
ELSE
INSERT INTO TempSales2_Changes_Log SELECTRecID, 'I', GETDATE() FROMINSERTED;
END
ELSE
BEGIN
INSERT INTO TempSales2_Changes_Log SELECTRecID, 'D', GETDATE() FROMDELETED;
END
March 1, 2012 at 4:44 am
Well, note that with that trigger you're doing more than twice times the work of an update without it and affecting twice the rows, so without the trigger will be faster. If the 2.7 million rows * 2 causes log file growth or data file growth that's even more time.
That's the kind of trigger you would probably disable before doing a 2.7 million row update, unless for whatever reason you did need to audit changes for all of those rows.
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
March 1, 2012 at 5:38 am
GilaMonster (3/1/2012)
Well, note that with that trigger you're doing more than twice times the work of an update without it and affecting twice the rows, so without the trigger will be faster. If the 2.7 million rows * 2 causes log file growth or data file growth that's even more time.That's the kind of trigger you would probably disable before doing a 2.7 million row update, unless for whatever reason you did need to audit changes for all of those rows.
If the OP compared time between "with trigger" to "using merge", and in both cases he did insert as many as 2.7 million rows into Audit Log (looks like he does update - not insert, so no duplication) then log and data files growth would be equal, so time would be almost the same. He reported 147 times difference!!! Check his trigger, the bottom part:
INSERT INTO TempSales2_Changes_Log
SELECTRecID,
@Mode,
@LogDate
FROMDELETED AS d
WHEREd.RecID NOT IN (SELECT i.RecID FROM INSERTED AS i);
I guess for 2.7 million updated records, this "nice" bit of logic with "NOT IN", would not be exactly fast :w00t:
BTW, I would encrease the size of data file to accomodate all audit log records to be inserted before running such an update. Also, usually, I would batch update of such size, so it would not fill up my log...
I kind of agree that 2.7 million rows update may not always require the auditing, but it all depends...
March 1, 2012 at 5:39 am
Eugene Elutin (3/1/2012)
Can you try this:
i
After making the changes as per your revisions to the trigger, the results are in!
The original run with the old trigger code took 1 hour 36 mins to run.
The revised trigger has cut this execution time down to just 18 minutes 53 seconds!
OK, so using this new trigger code causes the update to take 29 tikmes longer than an un-triggered update, but the improvement is dramatic over the old code.
Could you please clarify where the key speed increases were made?
March 1, 2012 at 5:42 am
Check my previous post, please. The slowest part of your trigger is using "NOT IN" (for no reason)!
Can you please advise, what have you actually mesured? The time taken "with trigger" vs "update with merge", or
just time taken "with trigger" vs "without trigger", as the last will be very different indeed, as it will not insert 2.7 million rows into audit log, isn't it?
March 1, 2012 at 7:19 am
Eugene Elutin (3/1/2012)
If the OP compared time between "with trigger" to "using merge", and in both cases he did insert as many as 2.7 million rows into Audit Log (looks like he does update - not insert, so no duplication) then log and data files growth would be equal, so time would be almost the same. He reported 147 times difference!!!
He compared the update with trigger to update without trigger. Not update with trigger to merge.
Example, the above 2.7 million row update with the trigger enabled took 1 hour 36 minutes to complete.
With the trigger disabled, the same run took a mere 39 seconds!
I guess for 2.7 million updated records, this "nice" bit of logic with "NOT IN", would not be exactly fast :w00t:
No, that's why I said "more than twice the work". That NOT IN will just be two scans with an anti-semi join combining them.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply