Adam Kinney on .NET

Hunting Godzilla in .NET...

Triggers == Bad Performance?

I was reading the new MSDN Magazine and came upon an article on SQL Triggers written by John Papa.  He does a good job of explaining what they are and how they work.  I would like to bring attention to the second to last paragraph called Performance.

I've “heard” and “been told” that triggers are bad for performance.  John does a good job of spelling out when they are “the right tool for the job”.  Following are a few good tips he gave:

  • Triggers are not slow, the logic inside is what causes the perf hit.  (e.g. cursors, multi-table traversal, ..)
  • Try to just work with the dynamic inserted and deleted tables
  • Triggers are compiled
  • Do not return resultsets
  • Keep it simple

Sounds good to me.  As long as I don't put too much weight on the backs of the triggers, and try and avoid confusion by having them talk to each other in complex loops; I think I can now use them with a clear conscience and I won't worry about causing my SQL server to die.

Posted: Nov 14 2003, 02:14 AM by AdamKinney | with 7 comment(s)
Filed under:

Comments

Duncan said:

The rule of thumb we work to is that triggers enforce relational integrity but stored procedures enforce business logic...seems to be about right.
# November 14, 2003 6:51 AM

Phil Scott said:

I've seen people use triggers where a check could have worked. For example, on an ON INSERT trigger they'll have code checking to see if the OrderDate <= GETDATE(), which could easily have been written as a check.

Preemptive integrity checks are always better than having to rollback a transaction.
# November 14, 2003 8:06 AM

Darrell said:

To add to what Phil said, you can have some pretty complex logic in check constraints. Hopefully we'll be able to use a .NET language to create check constraints in Yukon, which would be even more powerful.
# November 14, 2003 9:06 AM

denny said:

the "problem" as said is like the "If all you have is a hammer then everything looks like a nail" syndrome.

happens all the time in software development.

someone learns how to do something and keeps doing it that way.... ad-infinitum.

like moving from client side loops to a sproc with a cursor loop without understanding that a set-based solution might be right there if they would learn how to use set based logic.

so the coder thinks "hey this is better, look boss no more trips back to the client side and it's faster"
and misses "wow no loops and its 500% faster!!! Im L33t now!!!" opertunity.

:-)
# November 14, 2003 9:46 AM

HumanCompiler said:

That was definitely my problem. As you mention from the article, "keep it simple" and when I used triggers I sure didn't and yah...it was bad....good post!
# November 14, 2003 2:05 PM

Garstor said:

Beyond the coding issues mentioned here, there is of course, overall server performance. I've learned quite a few techniques to monitor that recently.

SQL Profiler is worth your weight in gold usually. But running it from the GUI incurs overhead. You can start a Profile trace with pure T-SQL commands from Query Analyzer or the OSQL command prompt.
# November 15, 2003 2:29 PM

Zina said:

Sorry. My philosophy is that not only are you responsible for your life, but doing the best at this moment puts you in the best place for the next moment. Help me! Can not find sites on the: Internet share trading. I found only this - <a href="tt.tlu.ee/.../ShareTrading">share trading permission</a>. Demand cash is also however dense, necessarily if the investors being continued are combined by pitfall, share trading. Share trading, as we have involved in a recently local exit. Thanks :-(. Zina from Zambia.

# March 23, 2010 11:12 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)