Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Red face Unanswered: Trigger vs Stored Procedure

    Hi,
    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.
    Thanks!!
    Joydeep

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The process is pretty simple:

    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:
    Code:
    DECLARE @t1 DATETIME, @t2 DATETIME
    
    SET @t1 = GetDate()
    
    --  Do something worth timing
    
    SET @t2 = GetDate()
    
    SELECT DateDiff(ms, @t1, @t2)
    -PatP

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thanks a lot for your help.
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •