Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Answered: Performance Wise Which Way Is Better

    I need to receive an email each time a record is inserted into a table where Rehirable = "Yes" My question is, performance wise would I be better creating a trigger on insert to generate the email or create an on insert trigger to run a sql job to send the email? (Or is there a better way all together to do this?)

  2. Best Answer
    Posted by Pat Phelan

    "Best performance and best practice is to decouple the email from the row change. The way to do this is to avoid the trigger altogether and to periodically (every so many minutes, hours, whatnot) run a query to find any "new" rows that need to be processed. One trivially easy way to do this is to create a column that shows when a given row was reported (as a datetime), and when the job runs check for NULL values that ought to be reported... This serves two purposes, it makes it easy to identify which rows need to be reported and it makes it simple to audit and verify that the rows were reported and gives you a date and time to start hunting for the email message too!

    Second place would be to have the trigger start a job.

    Following in dead last place is to have a trigger send an email directly.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Best performance and best practice is to decouple the email from the row change. The way to do this is to avoid the trigger altogether and to periodically (every so many minutes, hours, whatnot) run a query to find any "new" rows that need to be processed. One trivially easy way to do this is to create a column that shows when a given row was reported (as a datetime), and when the job runs check for NULL values that ought to be reported... This serves two purposes, it makes it easy to identify which rows need to be reported and it makes it simple to audit and verify that the rows were reported and gives you a date and time to start hunting for the email message too!

    Second place would be to have the trigger start a job.

    Following in dead last place is to have a trigger send an email directly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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