Please help me to find this answer.
We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table.
We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied.
But what about triggers?
what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers?
Please comment if anybody knows the answers.
1) Decide on what kind of trigger you want to create (INSERT, UPDATE, DELETE)
2) Create a scratch table as simliar as possible to your target table
3) Time the operation (INSERT, UPDATE, DELETE) with at least 1000 rows of data
4) Add a completely empty trigger, one that does nothing at all.
5) Time the operation with an empty trigger to find the raw "trigger fire" overhead.
6) Replace the empty trigger with one that does what you need
7) Time the operation with the trigger in place.
For these purposes, the method of timing is critical. All of the timing needs to be done by the server, on the server. All operations should be timed at least five times. The fastest and slowest times should be ignored, and the other times should be averaged. I recommend using code like:
DECLARE @t1 DATETIME, @t2 DATETIME
SET @t1 = GetDate()
-- Do something worth timing
SET @t2 = GetDate()
SELECT DateDiff(ms, @t1, @t2)