为什么尽量避免使用触发器？

View Post

   上次没有具体说明，宽宽一直在追问这个问题，现在补充如下：
     如果你是使用的SQL2000，这个问题会更加严重。触发器操作要作为外部事务的一部分，因此instered和deleted两个虚拟表都是写到事务日志中的。因为日志是顺序写入的，所以在把新旧记录写入日志时，会阻止其它事务写入。同时，读取日志时也会因为有其它写入时而被阻塞。这个无疑给并发操作带来很大影响。
     SQLSERVER在内部会把触发器作为一个存储过程来对待，除了不能输入参数等限制外。另一个值得考虑的问题是触发器的执行计划问题，它不随过程的重新编译而编译。上次演示过因为错误的缓存导致的错误的执行计划示例，不知道这个算不算作一个问题。
     现在SQL2005的新旧记录不再保存在日志中，而是使用新的行版本技术存储于tempdb中。这带来了一定程度上的并发优势，但是维护这些版本记录同样也需要额外的开销。同时，在这样的版本链列表中寻找记录也一样会有性能损耗。现在2005的update、delete、insert操作都带了output子句，可以使用它来替换触发器的操作。但是output有个限制是不能直接into到有约束的表，如果有约束也可以先把前后记录存入一个临时的地方再做进一步处理。
    下面的代码演示了使用output和trigger时，所观察到的情况，两者都会用到tempdb。但是output是在自己独有的空间中存储这些记录的，而trigger则是在一个公用的空间中存储就像是私家车库与公用停车场一样。因此output会更专职于处理某个请求的操作。具体代码如下：

--更新所有在London的供应商的产品体格为原价格的1.5倍

USE Northwind;

--创建价格变动历史记录表

IF OBJECT_ID('Price_history','U') IS NOT NULL

DROP TABLE Price_history

SELECT 1 AS ProductID,UnitPrice AS OldPrice,UnitPrice AS NewPrice,GETDATE() AS Date

INTO Price_history

FROM dbo.Products

WHERE 1=0

IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL

DROP TRIGGER trg_Products_u;

--sys.dm_tran_version_store用于存储行版本记录所用，此记录在没有被引用的情况下在一分钟内会被清理线程清除

select * from sys.dm_tran_version_store--确保此时没有版本记录存在

--请确保Products表现在没有任何其它更新触发器存在

--更新完成后发现没有记录相应的行版本，

--如果你打开性能计数器跟踪SQLSERVER:TRANSACTIONS对象的free space in tempdb，你会发现它确实也用到了tempdb。

--因为这个更新没有多少记录，看不出结果。你可以换一个Sales.SalesOrderDetail表来试一下。

update p

set UnitPrice=UnitPrice*1.5

output deleted.ProductID,deleted.UnitPrice,inserted.UnitPrice,getdate() into price_history

from dbo.Products p

join dbo.Suppliers s

on p.SupplierID=s.SupplierID

where s.city=N'London'

select * from sys.dm_tran_version_store

--现在我们创建一个更新触发器来完成此功能

IF OBJECT_ID('trg_Products_u','TR') IS NOT NULL

DROP TRIGGER trg_Products_u;

CREATE TRIGGER trg_Products_u ON dbo.Products FOR UPDATE

--如果更新的不是UnitPrice或没有更新直接返回

IF NOT UPDATE(UnitPrice) OR @@ROWCOUNT=0

RETURN;

ELSE

INSERT INTO price_history

SELECT i.ProductID,d.UnitPrice,i.UnitPrice,getdate()

FROM inserted i

join deleted d

on i.ProductID=d.ProductID

--使用触发器时，完成更新查看版本记录中有6条记录

--因为在'London'的供应商有三个产品，所以新旧记录加起来总共是6条记录

update p

set UnitPrice=UnitPrice*1.5

from dbo.Products p

join dbo.Suppliers s

on p.SupplierID=s.SupplierID

where s.city=N'London'

select * from sys.dm_tran_version_store

DBCC FREEPROCCACHE;--清除过程缓存以观察触发器的缓存计划

--创建显示重新编译的存储过程

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF OBJECT_ID('prc_UpdateProductPrice','P') IS NOT NULL

DROP PROC prc_UpdateProductPrice;

CREATE PROCEDURE prc_UpdateProductPrice

WITH RECOMPILE

BEGIN

SET NOCOUNT ON;

update p

set UnitPrice=UnitPrice*1.5

from dbo.Products p

join dbo.Suppliers s

on p.SupplierID=s.SupplierID

where s.city=N'London'

END

EXEC prc_UpdateProductPrice

--反复执行上述过程后，发现触发器的执行计划不会因为过程的重新编译而被重新编译

--这可能会因为缓存的原因，造成优化器错误的选择了执行计划

--不知道这个结果是喜是忧

SELECT usecounts, cacheobjtype, objtype, text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

posted on 2008-06-25 03:35 凉面阅读(2168) 评论(17) 编辑收藏所属分类: SQL Server 性能优化

社区新闻新用户注册刷新评论列表

标题

姓名

主页

(只有博主才能看到)


验证码	* 看不清,换一张 [登录][注册]

内容(请不要发表任何与政治相关的内容)

Remember Me?

登录使用高级评论新用户注册返回页首恢复上次提交

[使用Ctrl+Enter键可以直接提交]

该文被作者在 2008-07-11 19:18 编辑过

相关链接:

所属分类的其他文章:
使用ad hoc的利弊
数据库分页操作
为SQLSERVER打好地基－硬盘碎片和索引碎片
磁盘I/O的性能评估方法
结果集大小如何影响并发性
控制表连接及排序操作
过程中是否该显示删除临时表
如何正确使用事件探察器解决性能问题
如何有效利用索引编写高效过程

最新IT新闻:
淘宝网合并阿里妈妈专家称阿里巴巴或有新战略
微软研究院发布 AutoCollage - 整理并融合照片
2008年9月5日科技博客精选
SNS网站风靡影响工作效率公司下令封杀
《孢子》正式发布

博客园新闻频道博客园首页社区

怀疑一切，但不否定一切

与我联系

搜索

常用链接

留言簿

随笔分类

随笔档案

最新评论

阅读排行榜

评论排行榜

View Post

为什么尽量避免使用触发器？

View Comments

#1楼  回复 引用 查看

#2楼  回复 引用 查看

#3楼  回复 引用

#4楼  回复 引用 查看

#5楼  回复 引用 查看

#6楼  回复 引用 查看

#7楼  回复 引用

#8楼  回复 引用

#9楼  回复 引用 查看

#10楼  回复 引用 查看

#11楼  回复 引用 查看

#12楼  回复 引用 查看

#13楼  回复 引用

#14楼  回复 引用

#15楼  回复 引用 查看

#16楼  回复 引用 查看

#17楼  回复 引用