February 10, 2009 at 10:29 am
I need to check the performance of Trigger and Stored Procedure.
Is Trigger performs better or Stored Procedure? Which one takes more time to execute?
How can I check the performance of Trigger versus Stored Procedure?
Is there any option in SQL Profiler to check Trigger performance?
February 10, 2009 at 11:36 am
A trigger is event-driven on actions against your table. A stored procedure needs to be called by something.
They typically do not serve the same purposes. Perhaps you could post some information about what you are trying to do and you may get some advice as to how it may best be solved.
February 10, 2009 at 11:40 am
Performance differences aren't the reason to chose one over the other. Purpose is the defining characteristic that separates procs from triggers.
Do you have a specific issue that you're trying to resolve, or are you just trying to figure out the difference between the two?
- 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
February 10, 2009 at 1:16 pm
I read in some blog that Stored procedure execute faster than trigger. And I am trying to call stored procedure inside the trigger to perform some action instead of writing SQL in trigger.
For example trigger is looks like below:
CREATE TRIGGER [TestTrigger1] ON [dbo].[Account] FOR UPDATE AS
UPDATE Loan SET UPBAmount = Inserted.Amount
FROM Inserted INNER JOIN Loan ON SourceTable= 'Account' AND SourceColumn = Inserted.AccountID
-----------------------------------------------------------------
I am modifying this trigger and callinf the stored procedure to execute UPDATE statement
ALTER TRIGGER [TestTrigger1] ON [dbo].[Account] FOR UPDATE AS
DECLARE @SourceTable nvarchar(50),
@SourceColumn nvarchar(50),
@Amount money
SELECT@SourceTable = 'Account',@SourceColumn = Inserted.AccountID,@Amount= Inserted.Amount FROM Inserted
EXECUTE pUpdateAccount @SourceTable,@SourceColumn,@Amount
-------------------------------------------------------------------
And the stored procedure is
ALTER PROCEDURE [dbo].[pUpdateAccount]
@SourceTable nvarchar(50),
@SourceColumn nvarchar(50),
@Amount money
AS
UPDATE Loan SET UPBAmount = @Amount
FROM WHERE SourceTable= @SourceTable AND SourceColumn = @SourceColumn
---------------------------------------------------------------------------------------------
This is my thought and Please tell me is there any performance improvent with this?
And how to check the trigger performance (or execution details) in SQL Profiler?
February 10, 2009 at 1:44 pm
jagadish_sds (2/10/2009)
I read in some blog that Stored procedure execute faster than trigger.
That statement happens to be incorrect, assuming both use the same code.
You can prove this by using "set statistics time on". The test goes something like this:
set nocount on;
go
create table dbo.ProcTest (
ID int identity primary key,
Val1 int,
Val2 int);
go
insert into dbo.ProcTest (Val1)
select checksum(newid())
from dbo.Numbers;
go
create proc dbo.ProcTestProc
as
update dbo.ProcTest
set Val2 = Val1
where Val1/5 = Val1%5;
go
create table dbo.TriggerTest (
ID int identity primary key,
Val1 int,
Val2 int);
go
insert into dbo.TriggerTest (Val1)
select Val1
from dbo.ProcTest;
go
create trigger TriggerTestTrigger on dbo.TriggerTest
after insert
as
update dbo.TriggerTest
set Val2 = Val1
where Val1/5 = Val1%5;
go
set statistics time on;
begin transaction;
insert into dbo.ProcTest (Val1)
select 5;
exec dbo.ProcTestProc;
commit;
print '=======================';
begin transaction;
insert into dbo.TriggerTest (Val1)
select 5;
commit;
If you run the final execution tests a few times, you'll probably find that the run-time average is pretty much identical. That's what I found on my machine.
- 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
February 10, 2009 at 1:51 pm
Triggers are just event driven stored procedures. functions are limited versions of stored procedures, right?
for any identical code, all three should perform at the same speed, same execution plan, same statistics, same everything.
Lowell
February 10, 2009 at 3:49 pm
As written - your trigger is only valid when you can guarantee (for all time) that there will only ever be a single row updated at a time.
If you try to update more than a single row - the results are not going to be what you expected. To get what you want to work with a stored procedure, you would have to build a cursor over the inserted virtual table (bad idea) and call the stored procedure for every row in the update.
You'd be much better off just building the update statement directly in the trigger.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply