为什么 UPDATE 比 SELECT 花费的时间长得多?

2022-01-01 00:00:00 performance sql-server-2005 tsql sql-server triggers

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

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 的约束
  • ...

    • 虽然，通常的嫌疑人是触发器...

    Although, the usual suspect is a trigger...

    另外，你的条件 extra 没有意义:SQL Server 怎么知道忽略它?大多数行李仍然会生成更新......即使触发器仍然会触发.例如，在为其他条件搜索行时必须保持锁定

    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

相关文章