performance and number of variables in Triggers
-
does the Number of declared variables in a trigger affect heavely the performance of the transact sql. For instance if you have a trigger using 150 declared variables ( most of them are int). In our site we expect 200 users online firing this trigger.
(we use a table to fire (on insert) this trigger which affect many others tables)
is it the right way to programm the logic of our application?
-
Triggers should be as small as possible. I shudder at the thought of anythihng (especially a trigger) that requires that many variables.
> is it the right way to programm the logic of our application?
Hard to say without seeing the code. Could you post the definition of the trigger so we can take a look at it?
Remember that triggers always run in a transaction (created by the initial insert/update/delete) and so the locks created will not be released until the trigger is complete. It the trigger affects a lot of tables, you may find a large amount of blocking in your system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The number of variable declaration should not be the problem according to what you describe, but if you are affecting that many tables it looks like your logic is better suited for an SP instead.
Like GilaMonster above I also advice to keep the trigger code at a minimum if it is not possible to get rid of it altogether.
Can you use the logic in an stored procedure. I have found that refactoring application workflows tend to remove the need for a trigger (most of the time
)
* Noel
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply