Is SQL Server smart enough not to update UPDATE if the values โ€‹โ€‹are the same?

At work, we hacked into a stored procedure, and we noticed something.

For one of our update operators, we noticed that if the values โ€‹โ€‹match the previous values, we got a performance boost.

We did not talk

UPDATE t1 SET A=5 

where the column was already 5. We did something like this:

 UPDATE t1 SET A = Qty*4.3 

Anyway, is SQL Server smart enough not to perform the operation, if the values โ€‹โ€‹are evaluated equally in the UPDATE operation or are I just fooled by some other phenomena?

+6
source share
4 answers

Yes, you will see some performance gains. I recommend reading this article to get a better understanding (this will explain why it is much better than I can):

http://sqlblog.com/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx

+7
source

Judging by the TSQL output, he believes that UPDATE is executed even if it matches a single value.

 CREATE TABLE test (id INT, val int); GO INSERT INTO test VALUES(1, 1); GO (1 row(s) affected) UPDATE test SET val=1 WHERE id=1; GO (1 row(s) affected) 

When it comes to actually writing to disk, I certainly hope that it is not needed.

Edit: see answer from @AbeMiessler for a deeper analysis of how logging / disk writing works.

+2
source

You may see performance gains based on the specific state of your table indexes.

If the table is indexed and the update does not require that any data be moved (clustered), or no indexes should be changed (non-clustered), then you can see the gain.

If you tell SQL to upgrade, it will be updated. So, I would look at the hardware side (e.g. indexing).

+1
source

SQL will have to actually compute the numerical result before it does anything else, it must do it so that it knows what value it should "do something". Even then, for comparison, you need to read the value from the table.

What I'm trying to say is that if that were the case, it would actually make it less efficient to read the value, compare it with the one you are trying to update, and then decide whether it should perform the update operation. In your case, he must read Qty before he can determine what he needs to put in field A , but even then, by the time he compared the values, he could also complete the update and continue with the rest of his busy days :)

0
source

Source: https://habr.com/ru/post/907585/


All Articles