CODEWITHSUNDEEP
sqlsql-server
carlo.borreo
carlo.borreo

Reputation: 1355

UPDATE row when changed

I have a script that must update some fields in a big table, many times. In the main loop, I do queries like this:

UPDATE books SET title='ABC' WHERE code=123

I know that in more than 99.9% of the cases, the title will not be changed by the query, because it was already set to 'ABC'. I would like to make this loop as efficient as possible. Instead, I could do:

SELECT title FROM books WHERE code=123

then, compare the result with my string, and only when it is different perform the UPDATE. When the title changes, I would run one more query, but this is negligible because almost never the value needs to be changed.

My question is: would this be more efficient? In other words, is an UPDATE that cause no changes to a field more efficient than a SELECT that retrieves the same field?

Upvotes: 1

Views: 43

Answers (1)

Bauhaus
Bauhaus

Reputation: 509

Reading is generally more performant because it can be done in a non-blocking way by utilizing (NOLOCK) or by setting the transaction level to READ UNCOMMITTED

Updates are a blocking statement and will lock the row for the duration of the transaction even though the update field is not being changed.

Upvotes: 1

Related Questions