 | |||
|
|
||
| |||
| |||
| ||||||||
|
|
|||||||
|
|
|
|
| Feature: |
|
||
|
|||
|
|||
|
|||
|
|||
|
|||
|
|
|||||||||||||||||||||||||||||||||||||||||||||
Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 Problem: You already have a trigger on the table. You have to add new functionality to the trigger. You wonder if performance will improve if you add functionality to the existing trigger or maybe the best option is to create another trigger. What would you do? Solution: I ran the following example in order to compare performance and resources usage between the two methods for the same functionality: • Using only one trigger • Using two triggers Create tables
-- Create 3 tables with the same schema:
create table Table_1
(Col_1 int identity (1,1) PRIMARY KEY,
Col_2 varchar(10) NOT NULL,
Col_3 datetime NOT NULL default getdate())
go
select convert(int,Col_1) as Col_1, -- I use the convert to eliminate the
Identity property.
Col_2, Col_3 into Table_2
from Table_1
go
select convert(int,Col_1) as Col_1, Col_2, Col_3
into Table_3
from Table_1
go
Common script to run
-- Truncate 3 tables truncate table Table_1 truncate table Table_2 truncate table Table_3 go -- Clean SQL SERVER memory: dbcc dropcleanbuffers go -- Run 10000 inserts declare @i int set @i = 1 while @i <= 10000 begin insert into Table_1 (Col_2) values(convert(varchar(10),@i)) set @i = @i + 1 end go -- Create another table to hold the 10000 rows: Select * into Table_4 from Table_1 go -- Truncate and clean memory again: truncate table Table_1 truncate table Table_2 truncate table Table_3 go dbcc dropcleanbuffers go -- Run 10000 inserts at once: insert into Table_1 (Col_2, Col_3) select Col_2, Col_1 from Table_4 order by Col_1 Go Case 1: Create one trigger
-- Create one trigger with two inserts: create trigger trg_I_Table_1 ON Table_1 FOR INSERT as insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go Case 2: Create two triggers
-- Create two triggers – one with each command: create trigger trg1_I_Table_1 ON Table_1 FOR INSERT as insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go create trigger trg2_I_Table_1 ON Table_1 FOR INSERT as insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go I ran the common script six times for each case -- three times cleaning SQL Server memory and three times without cleaning it -- and here are the results I received from SQL Profiler:
If you end up with a very long trigger, you should split it up it is easier to flush a smaller unit from memory as needed (or recompile it again when necessary). Otherwise, if you are not sure which solution is best in a specific case, just test it as I have demonstrated.
| ||||||||||||||||||||||||||||||||||||||||||||||
Home 
Michelle Gutzait works as a team member of the SQL Server Database Experts at 
