Use of Trigger on a table which could have 100 hits per second. Will it hamper performance?

1

Hi All,
Currently in one of our project we have a situation.
The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. SQL server 2008 R2.
We need to implement database Trigger on a table which could have around 100 hits (for fetch and DML) per second. Trigger will work on Insert, Update and Delete. Wanted to know will the system performance go considerably due these triggers?

2

Hi,
I believe most people here will say it depends :smile: The reason being we would not know how peak your system is, and it might can still hold.

One thing for sure is that it will be slower for each DML (update, insert, delete) as depends on what your trigger will be (how complex), it will have a performance impact. Just imagine insert of doing 1 DML, you now doing 2 or more (whatever in the trigger), if the trigger is a very very simple one, you might only get a small impact as it might just perform 2 query instead of one, but if its complex, you will see the impact in a much larger scale.

Be sure to test it, in a big scale, and make sure you can accept the impact before rolling it out.

hope this helps

3

It depends on how complex your trigger code is, but in case of any "reassurance" :slight_smile: we have a trigger on every table in our database which does two things:

For the Create Date and Create User columns, if they are NULL, then set a value of "now" and "current user".

For every varchar column Trim the value, and if the value is blank-string then change it to NULL.

So in effect we re-update EVERY row we insert/update - even if there are no material changes (I know, terrible isn't it ...)

Also, for an UPDATE or DELETE we store the original row's column values into an Audit table. That's just a simple INSERT, but it happens for every UPDATE / DELETE.

We have a lot more then 100 trigger-fires per second, and some of them will be for very large numbers of rows.

Important to note (just in case you didn't know it) that you must write your trigger to handle multi-rows as single action. The trigger is NOT fire for each row being inserted / updated / deleted, it fires once for the whole SET of data - so you just have to write your trigger accordingly. IF you can only program your trigger to loop round each row individually THEN IT WILL DEFINITELY BE SLOW when it has to process many rows - best to avoid that situation :slight_smile: