Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vignesh
Starting Member
20 Posts |
 Posted - 2006-03-27 : 09:56:16
|
| hello allI am using a trigger on a table xyz. This trigger is fired on after insert. In the table xyz the insert rate is very high say about 10 to 15 records every second in the peak time.I am writing a trigger which will pick up a value (cost) from table xyz and update a value (balance) in table abcNow i would like to know about the performance issues with the insert in table xyz. Will it be affected. If yes then by how much amount it will get slowThanks in advanceVignesh |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-27 : 10:08:30
|
| it depends on what's involved with calculating the balance and performing the update to the abc table. Triggers can have a huge performance impact on tables with that much activity. If possible, I like to limit triggers things like auditing and queuing rows for processing. It's usually not a good idea to use them for performing aggregations.write the code to perform the calculation and the update, run it (manually) and look at the duration in milliseconds. That will be an example of how much time will be added to each insert. That duration won't take into account the added time because of contention that will occur from holding the locks for a longer period of time.Be One with the OptimizerTG |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-27 : 10:20:00
|
| Problem with doing that is usually that the update of the balance takes time and also that people may be querying balances and locking the table.I always try to do this by having the trigger insert into a table and a scheduled job (every minute/5 minutes?) accessing that table and applying thenn deleting all the entries (don't do it row by row).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Vignesh
Starting Member
20 Posts |
Posted - 2006-03-29 : 01:22:24
|
| thanks a lot for your replies. yes i think a scheduled job would be a better option. i will test it and let you know about itVignesh |
|
|
|
|
|
|
Triggers and Performance