July 31, 2009 at 10:51 am
Hi everyone.
I've been debating with a teammate about some functionality,
The situation is this.
We have an aplication that read data from an excel file then when an update ocurrs on certain data a trigger is activated to update a field in another table. Simple as that.
He says, " if we know that the data will ALWAYS be updated why not just call a store procedure with the parameters to do the trigger work, so we shutdown the trigger"
Both work, but now i have the doubt about...
¿Wich one have a better perfomance, call a store procedure sending parameters from excel, or leave the trigger working with the inserted data?
Any info would be appreciated. 😀
July 31, 2009 at 11:14 am
If it's the same code, the performance will probably be the same.
The only way to know for sure would be for you to set up a test and try it out.
- 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
July 31, 2009 at 5:27 pm
I've always had the impression that Triggers had more overhead and would be slower than stored procedures. Of course, depending on how long this Excel File work takes, it may not be noticable.
And, IMHO, a stored procedure is better from a development and maintenance standpoint ("no suprises").
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2009 at 8:52 am
I can think of 1 reason to keep it in a trigger, someone does an update to that column outside the excel spreadsheet.
As far as performance, the only issue I can see is that in a trigger the code runs withing a single transaction so it will make the transaction a bit longer. In a stored procedure you could do explicit transactions, although if the second update fails you likely want the first rolled back as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 3, 2009 at 11:48 am
Is correct, i didn't thought on that, but thats a real condition to not to move into a SP, we left the trigger because the proces time is shorter, but the dirence becomes notable when the excel file is bigger than 1k rows, sending excel data to a SP seems to consume more time, even if the trigger and store have almost the same code, just diference between the source of the parameters.
Thanks for the opinions, now i can fight with the new assignment without thinkin if i should change or not this one :D. I really hate to have to modify my work when it is already in production D: .
August 4, 2009 at 7:24 am
RBarryYoung (7/31/2009)
I've always had the impression that Triggers had more overhead and would be slower than stored procedures. Of course, depending on how long this Excel File work takes, it may not be noticable.And, IMHO, a stored procedure is better from a development and maintenance standpoint ("no suprises").
In SQL 2000, triggers were often slower than equivalent procs, because of use of the inserted/deleted tables, which involved reading data from the transaction log. In 2005/8, that isn't how those tables work any more, and triggers are faster than they used to be.
Here's a test to illustrate this:
SET nocount ON ;
go
IF NOT EXISTS ( SELECT
*
FROM
master.sys.databases
WHERE
name = 'ProofOfConcept' )
EXEC ('create database ProofOfConcept;') ;
USE ProofOfConcept ;
go
IF OBJECT_ID(N'dbo.ProcTest2') IS NOT NULL
DROP TABLE dbo.ProcTest2 ;
IF OBJECT_ID(N'dbo.ProcTest1') IS NOT NULL
DROP TABLE dbo.ProcTest1 ;
IF OBJECT_ID(N'dbo.TriggerTest2') IS NOT NULL
DROP TABLE dbo.TriggerTest2 ;
IF OBJECT_ID(N'dbo.TriggerTest1') IS NOT NULL
DROP TABLE dbo.TriggerTest1 ;
IF OBJECT_ID(N'dbo.Proc1') IS NOT NULL
DROP PROC dbo.Proc1 ;
IF OBJECT_ID(N'dbo.Proc2') IS NOT NULL
DROP PROC dbo.Proc2 ;
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
go
CREATE TABLE dbo.ProcTest1 (
ID INT IDENTITY
PRIMARY KEY,
Col1 VARCHAR(10)) ;
go
CREATE TABLE dbo.ProcTest2 (
ID INT IDENTITY
PRIMARY KEY,
PT1ID INT NOT NULL
REFERENCES dbo.ProcTest1 (ID) ON DELETE CASCADE ) ;
go
CREATE PROC dbo.Proc1 (@Col1_in VARCHAR(10))
AS
INSERT INTO
dbo.ProcTest1 (Col1)
SELECT
@Col1_in ;
INSERT INTO
dbo.ProcTest2 (PT1ID)
SELECT
SCOPE_IDENTITY() ;
go
CREATE TABLE dbo.TriggerTest1 (
ID INT IDENTITY
PRIMARY KEY,
Col1 VARCHAR(10)) ;
go
CREATE TABLE dbo.TriggerTest2 (
ID INT IDENTITY
PRIMARY KEY,
PT1ID INT NOT NULL
REFERENCES dbo.TriggerTest1 (ID) ON DELETE CASCADE ) ;
go
CREATE TRIGGER dbo.Trigger1 ON dbo.TriggerTest1
FOR INSERT
AS
INSERT INTO
dbo.TriggerTest2 (PT1ID)
SELECT
ID
FROM
inserted ;
go
CREATE PROC Proc2 (@Col1_in VARCHAR(10))
AS
INSERT INTO
dbo.TriggerTest1 (Col1)
SELECT
@Col1_in ;
go
CREATE TABLE #T (
ID INT IDENTITY
PRIMARY KEY,
Test VARCHAR(10),
StartTime DATETIME NOT NULL
DEFAULT (GETDATE()),
EndTime DATETIME,
RunTime AS DATEDIFF(millisecond, StartTime, EndTime)) ;
go
INSERT INTO
#T (Test)
SELECT
'Proc' ;
go
EXEC dbo.Proc1 'AAAAA' ;
go 10000
UPDATE
#T
SET EndTime = GETDATE()
WHERE
Test = 'Proc' ;
go
INSERT INTO
#T (Test)
SELECT
'Trigger' ;
go
EXEC dbo.Proc2 'AAAAA' ;
go 10000
UPDATE
#T
SET EndTime = GETDATE()
WHERE
Test = 'Trigger' ;
go
SELECT
*
FROM
#T ;
On my machine, 10,000 iterations of the proc took 6.64 seconds, while 10,000 iterations of the trigger took 5.233 seconds.
I ran this multiple times, to eliminate variables like CPU load from other processes, and each time the trigger version was slightly faster.
It's on over-simplified, imperfect test, but it does show that proc code isn't necessarily faster than trigger code.
It does still have the disadvantage that the trigger code ends up "hidden". It makes it harder to maintain the database and to refactor it. It has the advantage that it can't be easily bypassed by other processes, and that it can handle any number of rows being inserted into the base table. You have to weigh those against each other, of course.
- 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply