Geek asks and answers

SQL Server Performance - IF Select + Update vs Update

I am writing a large script full of update statements that will be run daily. Some of these updates will affect strings that others will not.

I believe that statements about updates that will not affect anything are not very good practice, but my question is:

  • How much can this affect performance? Is it not at all like if you select statements and only update when you select something?

Thanks Tiago

+5
source share
3 answers

, SELECT, UPDATE, concurrency. SERIALIZABLE, , SELECT UPDATE.

-, , , SELECT + UPDATE , UPDATE.

, , UPDATE, SELECT, . "", "", , (concurrency), .

, SELECT, UPDATE, 100% . SELECT UPDATE . (SELECT , , UPDATE , ) (SELECT , , / UPDATE ).

, . UPDATE, .

+3

, . (..)

UPDATE targetTable
FROM targetTable INNER JOIN sourceTable

WHERE , , , .

, , , - ...

UPDATE targetTable SET column = column

... , UPDATE. 100%, , , , .

+1

, UPDATE … WHERE condition SELECT … WHERE the_same_condition + UPDATE … WHERE the_same_condition , , , . UPDATE SELECT, , SELECT … WHERE … , .

But @Matt Whitfield makes sense about triggers. If there is a trigger during update, it will fire regardless of whether any rows are updated. And if there is something that triggers the trigger, even if there are no updated lines (and you would like to avoid it), then either rewrite your trigger, or, yes, go to the SELECT+ approach UPDATE.

+1
source

More articles:


All Articles