too many triggers
[447 byte] By [shuvo] at [2007-11-20 6:56:50] 
This database is for a web site,where the probablity is that , thousends of users will be always logged in and always entering and updating the tables.At this moment,there are triggers described on every insertion or updation event in the tables.So if hundreds of users continuously manipulate their data, then a great number of triggers will always be running in the database.Will this not effect the database and the webapplication performance?
«« setup project in VS.NET with dependency of >net frame work
»» Resolution Problem:if designed at falana resolution,and if i change
»» Resolution Problem:if designed at falana resolution,and if i change
# 1 Re: too many triggers
This will surely help you in understanding how to tune triggers.Tuning Triggers ( http://www.sql-server-performance.com/trigger_tuning.asp)
Shuja Ali at 2007-11-9 13:44:59 >
# 2 Re: too many triggers
thank U ali,but i am not concerned about tuning my triggers.Even thought i write well designed triggers,My concern is about the workload in the server.the overhead of thousends of triggers always runnin in the sqlserver engine,will it effect the performance of the application?shuvo at 2007-11-9 13:46:00 >
# 3 Re: too many triggers
My bad. I should have written it properly. when you have too many triggers and there are too many users accessing the same table, it will surely affect the performance. Imagine the Row Level Locks, Table level Locks that will make others to wait till the triggers are completed.The link that I posted above actually tells you how to avoid common triggers and what should be used instead. Please go though it once.
Shuja Ali at 2007-11-9 13:47:09 >
# 4 Re: too many triggers
Trigger are well know to cause performance issues. It would be better if you did some testing on your system. Try out will a huge set of insert into your tables programmatically and then time the performance. There should be tools specific to your database to measure these timings. Then you would be in a better state to predict any performance hits.Try the testing with alternative solutions at your disposal.
exterminator at 2007-11-9 13:48:05 >
# 5 Re: too many triggers
Of course, I dont know the details of YOUR application, but the following technique works well for most cases [including some Fortune 500 companies Web Sites]..I am going to assume you are running Windows [ASP/ASP.NET] and SQLServer 2005/2000. The technique will apply to other environments (I have seen it implemented for WebSphere for Example).
Implement a Queue between your WebSite and the actual Database. You "throttle" the hits to the database by controlling the number of listeners to the Queue. During non-peak usage, the queues flow freely, and the commit to the database is almost instant. During peak usage, the data flows as fast as possible without the DBMS getting clogged.
When I implement this I use MSMQ. This queuing mechanism is 100% reliable (meaning even if the system(s) crash, the data in the queue will remain intact.
With SQLServer (ideally 2005), you can actually embed this logic directly at the DBMS (assuming you are using 100% stored procedures for [at least write] access). This means that there are no changes to your WebSite!
Contact me off line if you want specific details [keep it online for general questions so all can benefit]
Regards :wave:
TheCPUWizard at 2007-11-9 13:49:05 >
