CODEWITHSUNDEEP
sqlsql-serverperformancetriggers
Kenny Evitt
Kenny Evitt

Reputation: 9801

Is there a performance penalty for a trigger with no code?

There is a trigger on a table for updates, inserts, and deletes, but the T-SQL create-script for the trigger contains only the following statement:

SET NOCOUNT ON

Is there a (significant) performance penalty for this trigger compared to no trigger? Would there be any (significant) performance penalty for a trigger with no statements (code), again compared to no trigger at all?

Upvotes: 1

Views: 383

Answers (2)

Stephen Turner
Stephen Turner

Reputation: 7314

Without an in depth knowledge of what the optimiser is doing, or running a bunch of tests to check it's hard to say. But then it's hard to say how significant your idea of significant is either.

So to take each extreme, if you are doing thousands of updates a second then the trigger could cause a significant drop in performance... On the other hand running a few updates a minute you probably won't notice at all. In either case the trigger will be compiled, and possibly optimised out of existence.

Myself I wouldn't bother about performance unless performance was bothering me. If you think it is causing you issues, measure it, remove it and remeasure.

In a similar vein, I had a DBA suggest that I should replace Coalesce(xyz, 0) with Isnull(xyz, 0). The query wasn't bothering me but the suggestion did, so I did some measurements and found that it was indeed 30% faster. But at 0.00000031 for each of the two coalesce callin in my query, I didn't alter my query ;-

Upvotes: 3

jj.
jj.

Reputation: 2390

This statement keeps extra messages from being passed back to the caller.

Imagine that you have 5 update statements in your trigger. If you didn't have the NOCOUNT line the caller would receive messages from all 5 statements. Something like this: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected)

If you have the NOCOUNT statement set, then they would only receive the end result message: (1 row(s) affected)

This isn't necessarily a bad thing - some systems don't care about the extra messages, and maybe your database isn't that large that you would care about any performance hit. Though some other systems do care - I know that I have to remember to use a NOCOUNT in stored procedures that are called from Reporting Services (SSRS).

Upvotes: -1

Related Questions