sql server - Why does an UPDATE take much longer than a SELECT?

Question

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  

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

Even if I add:

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?

Answer 1

• 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...

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

Edited Sep 2011 and Feb 2012 with more options