没有代码的触发器是否会造成性能损失？
表上有一个用于更新、插入和删除的触发器，但触发器的 T-SQL 创建脚本仅包含以下语句：
SET NOCOUNT ON
与没有触发器相比，此触发器是否存在（显着的）性能损失？与根本没有触发器相比，没有语句（代码）的触发器是否会有任何（显着的）性能损失？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果不深入了解优化器正在做什么，或者运行大量测试来检查，就很难说。但也很难说你的“重要”想法有多重要。
因此，就每个极端而言，如果您每秒执行数千次更新，那么触发器可能会导致性能显着下降......另一方面，每分钟运行几次更新您可能根本不会注意到。无论哪种情况，触发器都将被编译，并可能被优化以使其不存在。
我自己不会担心表现，除非表现困扰着我。如果您认为它给您带来了问题，请测量它，将其移除并重新测量。
同样，DBA 建议我将 Coalesce(xyz, 0) 替换为 Isnull(xyz, 0)。查询并没有困扰我，但建议却困扰了我，所以我做了一些测量，发现它确实快了 30%。但是对于我的查询中的两个合并调用中的每一个，在 0.00000031 处，我没有更改我的查询；-
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 ;-
此语句可防止额外的消息传递回调用者。
假设您的触发器中有 5 个更新语句。如果您没有 NOCOUNT 行，调用者将收到来自所有 5 个语句的消息。像这样的东西：
（受影响的 1 行）
（受影响的 1 行）
（受影响的 1 行）
（受影响的 1 行）
（1 行受影响）
如果您设置了 NOCOUNT 语句，那么它们只会收到最终结果消息：
（1 行受影响）
这不一定是坏事 - 有些系统不关心额外的消息，并且也许您的数据库没有那么大，您不会关心任何性能影响。尽管其他一些系统确实关心 - 我知道我必须记住在从 Reporting Services (SSRS) 调用的存储过程中使用 NOCOUNT。
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).