sql-server – 为什么UPDATE比SELECT要花费更长的时间？

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

但是,等效的更新语句需要1分40秒

declare @weekending varchar(6)
set @weekending = 100103
update InvoicesCharges  
set InvoicesCharges.orderaccnumber = Accountnumbersorders.accountnumber  
from Accountnumbersorders,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),12) = @weekending

即使我添加：

and InvoicesCharges.orderaccnumber <> Accountnumbersorders.accountnumber

在更新语句结束时将写入次数减少到零,需要相同的时间.

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

解决方法

虽然,通常的嫌疑人是触发器……

此外,您的条件额外没有意义：SQL Server如何知道忽略它？大部分行李仍然会产生更新……即使触发器仍然会触发.例如,在搜索行以查找其他条件时,必须保持锁定

2011年9月和2012年2月编辑,提供更多选项

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!