DTS Package vs Trigger

[wbodger]

I am trying to determine whether I should turn some of my DTS packages into triggers, so I am trying to find the performance differences between the two. Most of my DTS packages run a query searching for certain activities since it was last run and then (generally) send an email or perform an HTTP post. I am running this on SQL 2k sp4 with 10 gb of RAM running on Windows 2003 Server Enterprise Edition. How can I determine at what point it is a better use of system resources to use a trigger instead of a DTS package that runs a query every 2-60 minutes? I have others that just run the query once/day, so I would assume it would be more efficient to leave those as is (for instance, there are probably 50 transactions that fit the criteria for this one every day, so I run it once instead of firing off a trigger 50 times).

Does that make sense? Do I need to provide more info or post to a different forum? Thanks for any help you can give to me.

Willie

 

[RiverGuy]

A trigger isn't for executing a task at specific intervals, but rather, when something happens to trigger an event (row updated, deleted, etc.). Keeping this as a DTS package or converting it to a plain scheduled task would be your best bet.

 

[wbodger]

Right, a trigger is something that happens every time a qualifying event occurs. For instance, every time somebody purchases product X, a post is made to server y. Right now, I have a DTS Package that runs every 2 minutes to check for this, but it only has to make the post maybe 6 times per hour, so... Which is a better use of resources, to continue to run the DTS package every 2 minutes when it will frequently only run a query that returns an empty result set, or turn this into a trigger that will only fire 6-8 time in an hour (as opposed to the DTS package running 30 times in that hour). How do the use of system resources stack up?

 

[RiverGuy]

That system resources will depend on your database tables and query sent to them. How long do the queries take to complete when they run every two minutes? I'm not really a fan of coding non-TSQL actions into triggers. Another option is to code a trigger to write to a lightweight audit table which your DTS package can poll every two minutes.

 

[wbodger]

The queries actually run pretty quickly and the trigger would go off of an insert, so that is a TSQL action, is it not? Or are you referring to what the trigger does, not what triggers it? I really haven't down many triggers, so I'm not sure of the full impact on system resources, it just seems to me they are like cursors, they have their place, but they can also damage your system response times if not used properly. Would it be a bad idea to have a trigger that runs a stored procedure, so rather than running it on a schedule (in the DTS package), it would just run when the criteria are met? Or is that even do-able?

 

[RiverGuy]

Inserts into a database should be fast--I wouldn't want to add logic to send an email to the insert of a record. I would keep that as a separate process. Maybe even look into adding it to the application's logic after a successful insert.

 

[wbodger]

OK, so the trigger would add overhead to that transaction (every insert) which would mostly be unneeded overhead. Got it, makes sense. Not sure about adding it to the logic after an insert, could you give an example of how that would be accomplished? And thank you for the help, I appreciate it.

Willie

 

[RiverGuy]

This would be pseudo-code to put the logic into the application:

1.  Insert record into the database, and return Identity field value of new record if exists
2.  Was Insert Successful?
  -Yes
     1.  Call process (possibly in another thread, or add to queue
     in another assembly) passing primary key and attribute
     information user inserted.
     2.  Programming code sends emails/performs HTTP post, etc.
  -No
     Standard exception handling logic

 

[wbodger]

But isn't that a trigger? Upon successful insertion, do this?

 

[RiverGuy]

A trigger in the context of SQL Server is a database object, written in T-SQL. What I meant in the example was in the application's code, whether it's VB or Java, etc.--the code that is making the connection to the database. I know it is not always possible to alter the application, which is why I also like the Database trigger writing to a queuing table which the DTS package can query every x minutes. This way, the DTS package doesn't have to query the large OLTP table every two minutes for unprocessed records--it only has to query a small queuing table.

 

[wbodger]

Ahh, got it. Makes sense. Thx!

Willie