Oft running processes vs triggered processes

[wbodger]

So, I have a database facing the outward world (I know, poor database). Anyway, I have a number of processes that run every 5 minutes or so checking if any new registrations have occurred (or newly inserted products etc.). Not that I have a lot of these, but I was wondering if it would be better to perhaps have these set off by triggers as opposed to a scheduled DTS package that runs every few minutes? Pluses and minuses? Thoughts?

Willie

 

[mrdenny]

If it doesn't happen often then a trigger will be the better option. This will reduce over all server load and reduce complexety.

If it happens often you will probably want to patch it by having a job do the processing as this will decrease the insert / update time and decrease overall performance.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

Ahh.... good idea. I will monitor it for a while and see how often it really has anything to process. What would you consider a reasonable cutoff for trigger vs scheduled DTS package?

Willie

 

[mrdenny]

I would say probably that if the number of transactions is more than a couple per hour then go with the trigger. Less than that batch it, unless you can do without the data changes and you can afford to schedule it only to run a few times a day.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

That makes sense. I will go about looking into triggers a little more and get those going. Thank you for your help.

Willie

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com