为什么UPDATE比SELECT需要更长的时间？ [英] Why does an UPDATE take much longer than a SELECT?

问题描述

我有以下几乎立即完成的选择语句。

I have the following select statement that finishes almost instantly.

declare @weekending varchar(6)  
set @weekending = 100103

select InvoicesCharges.orderaccnumber, Accountnumbersorders.accountnumber  
from Accountnumbersorders, storeinformation, routeselecttable,InvoicesCharges, invoice   
where InvoicesCharges.pubid = Accountnumbersorders.publication  
and Accountnumbersorders.actype = 0  
and Accountnumbersorders.valuezone = 'none'  
and storeinformation.storeroutename = routeselecttable.istoreroutenumber   
and storeinformation.storenumber = invoice.store_number  
and InvoicesCharges.invoice_number = invoice.invoice_number  
and convert(varchar(6),Invoice.bill_to,12) = @weekending  

但是，等效的更新语句需要1m40s

However, the equivalent update statement takes 1m40s

declare @weekending varchar(6)
set @weekending = 100103
update InvoicesCharges  
set InvoicesCharges.orderaccnumber = Accountnumbersorders.accountnumber  
from Accountnumbersorders, storeinformation, routeselecttable,InvoicesCharges, invoice   
where InvoicesCharges.pubid = Accountnumbersorders.publication  
and Accountnumbersorders.actype = 0  
and dbo.Accountnumbersorders.valuezone = 'none'  
and storeinformation.storeroutename = routeselecttable.istoreroutenumber 
and storeinformation.storenumber = invoice.store_number 
and InvoicesCharges.invoice_number = invoice.invoice_number
and convert(varchar(6),Invoice.bill_to,12) = @weekending

即使我添加：

and InvoicesCharges.orderaccnumber <> Accountnumbersorders.accountnumber

将写入次数减少为零，它将相同的时间。

at the end of the update statement reducing the number of writes to zero, it takes the same amount of time.

我在这里做错了吗？为什么会有如此大的差异？

Am I doing something wrong here? Why is there such a huge difference?

推荐答案

• 事务日志文件写入


• 索引更新


• 外键查找


• 外键级联


• 索引视图


• 计算列


• 检查约束


• 锁


• 闩锁


• 锁定升级


• 快照隔离


• 数据库镜像


• 文件增长


• 其他进程读取/写入


• 页面拆分/不合适的聚集索引


• 前向指针/行溢出事件


• 索引不足


• 统计数据已过期


• 磁盘布局不佳（例如，一个大RAID可以容纳所有磁盘）


• 使用具有表访问权限的UDF检查约束


• ...


• transaction log file writes
• index updates
• foreign key lookups
• foreign key cascades
• indexed views
• computed columns
• check constraints
• locks
• latches
• lock escalation
• snapshot isolation
• DB mirroring
• file growth
• other processes reading/writing
• page splits / unsuitable clustered index
• forward pointer/row overflow events
• poor indexes
• statistics out of date
• poor disk layout (eg one big RAID for everything)
• Check constraints with UDFs that have table access
• ...

尽管，通常的嫌疑人是触发 ...

Although, the usual suspect is a trigger...

此外，您的病情没有任何意义：如何SQL服务知道忽略吗？大多数行李仍会生成更新，即使触发器仍会触发。在搜索行中查找其他条件时必须保持锁定状态，例如

Also, your condition extra has no meaning: How does SQL Server know to ignore it? An update is still generated with most of the baggage... even the trigger will still fire. Locks must be held while rows are searched for the other conditions for example

2011年9月和2012年2月编辑，带有更多选项

Edited Sep 2011 and Feb 2012 with more options

这篇关于为什么UPDATE比SELECT需要更长的时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！