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

Because reading does not affect indices, triggers, and what have you?

Answer 2

The update has to lock and modify the data in the table, and also log the changes to the transaction log. The select does not have to do any of those things.

Answer 3

• log file writes
• index update
• foreign key lookups
• foreign key cascades
• indexed views
• computed columns
• check constraints
• locks
• latches
• lock escalation
• snapshot isolation
• DB mirroring
• ...

Although, the usual suspect is a trigger...

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

Edit: Try the update with AND 1 = 0. What happens?