locked
Triggers vs. Jobs RRS feed

  • Question

  • Hello,

     

    I’m designing a database with a table that automatically gets loaded with a new record every 15 seconds (each record will have a 'timestamp' field and around 50 ‘float’ fields).  Each record in this table needs to be copied to another table.  I have done some initial testing with two different approaches to do this:

     

    1. Use a trigger to insert each record into the other table (will fire every time a record is added to the source table - every 15 seconds)

     

    2. Use a job (SQL Server Agent) to run every ten minutes or so and insert a batch of records into the other table (and use a flag field in the source table to indicate which records have been processed).

     

    I don’t have very much experience with triggers or SQL Server jobs so I don’t know the impact regarding overhead and system resources (over time, both tables will have a lot of records).

     

    In general, what approach would be better?  Is there another approach I should consider?

     

    Thanks

     

    Thursday, March 25, 2010 6:49 PM

Answers

  • Its safer to go with scheduled jobs to do this requirement when compared to trigger since trigger do it for each insert of the source table to target whereas scheduled job take a bulk copy and insert it into target which will reduce the entry of database logs as well.

     


    Regards, KarthikShanth. "Mark as Answer" if this helps you!
    • Marked as answer by aronsb Friday, March 26, 2010 6:04 PM
    Friday, March 26, 2010 8:00 AM
  • Triggers are expensive and take some sort of resources and it always recommended to use when there is really a need, that u cn't avoid.

    Creating a Job is much more feasible and manageable as u can keep track of it.

     

    Also if u compare the trigger with job this u will see that every time a insert is happening you are triggering the trigger so if 15 records inserted you will see trigger fired 15 times. Where in job will take all 15 transactional into a single transaction.

    Otherway you can setup transactional replication if u want to replicate the data to some other database on some other server on scheduled or real time basis .Replication is also resource hungry.will consume someamount of resource interms of CPU & RAM.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    • Marked as answer by aronsb Friday, March 26, 2010 6:03 PM
    Friday, March 26, 2010 5:40 PM

All replies

  • Before going for any approach could you please provide below information’s?

     

    1. Size of data you wants to transfer (Number of records)

    2. The data load with in the same server/two different servers/Remote server.

    3. Which type of information the data stores? (Master/Facts)

    4. Is the new data have some dependency or not?

    5. The minimum time takes when you test the load.

     

    Thanks,

    Sandeep

    Friday, March 26, 2010 7:19 AM
  • Its safer to go with scheduled jobs to do this requirement when compared to trigger since trigger do it for each insert of the source table to target whereas scheduled job take a bulk copy and insert it into target which will reduce the entry of database logs as well.

     


    Regards, KarthikShanth. "Mark as Answer" if this helps you!
    • Marked as answer by aronsb Friday, March 26, 2010 6:04 PM
    Friday, March 26, 2010 8:00 AM
  • 3. Have the process that loads the first TABLE also load the second TABLE.

     

    Excellent case for a stored PROCEDURE.

    Friday, March 26, 2010 12:50 PM
    Answerer
  • 1. One record every 15 seconds

    2. Data will be transferred from the source table to the target table (both are tables on the same SQL Server)

    3. Each record will have a 'datetime' field and around 50 'float' fields

    4. No dependencies

    5. I don't understand the question

    Thanks

     

    Friday, March 26, 2010 5:27 PM
  • Thanks for the reply, unfortunately, I don't have control over the process that loads the first table (a third-party application logs data and interfaces with SQL Server to create a table and add records).
    Friday, March 26, 2010 5:30 PM
  • Triggers are expensive and take some sort of resources and it always recommended to use when there is really a need, that u cn't avoid.

    Creating a Job is much more feasible and manageable as u can keep track of it.

     

    Also if u compare the trigger with job this u will see that every time a insert is happening you are triggering the trigger so if 15 records inserted you will see trigger fired 15 times. Where in job will take all 15 transactional into a single transaction.

    Otherway you can setup transactional replication if u want to replicate the data to some other database on some other server on scheduled or real time basis .Replication is also resource hungry.will consume someamount of resource interms of CPU & RAM.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    • Marked as answer by aronsb Friday, March 26, 2010 6:03 PM
    Friday, March 26, 2010 5:40 PM