Databases
StackOverflow2013
Postsdbo
POWhy does an UPDATE take much longer than a SELECT?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does an UPDATE take much longer than a SELECT?
    text
    copied!<p>I have the following select statement that finishes almost instantly.</p> <pre><code>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 </code></pre> <p>However, the equivalent update statement takes 1m40s</p> <pre><code>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 </code></pre> <p>Even if I add:</p> <pre><code>and InvoicesCharges.orderaccnumber &lt;&gt; Accountnumbersorders.accountnumber </code></pre> <p>at the end of the update statement reducing the number of writes to zero, it takes the same amount of time. </p> <p>Am I doing something wrong here? Why is there such a huge difference?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload