Alex Rivera | Logout

Is SQL Server smart enough not to UPDATE if values are the same?

Asked 2012-02-03T15:50:15.757
8

At work we've been hacking away at a stored procedure and we noticed something.

For one of our update statements, we noticed that if the values are the same as the previous values we had a performance gain.

We were not saying 

UPDATE t1 SET A=5

where the column was already was equal to 5. We were doing something like this: 

UPDATE t1 SET A = Qty*4.3

Anyway, is SQL Server smart enough not to do the operation if the values evaluate to the same in an UPDATE operation or am I just being fooled by some other phenomena?

sql-server database-performance
Edit
Report

2 Answers

3

You might be seeing some performance gains based on the specific state of your table indexes.

If the table is indexed, and the update doesn't require that any data is moved around (clustered) or no indexes need to be altered (non-clustered), then you might see a gain.

If you tell SQL to update, it's gonna update. So, I'd look towards the hardware side (e.g., indexing).

answered 2012-02-03T16:03:49.360
0

SQL will have to actually calculate the numerical result before it does anything else, it has to do this so it knows what value it's got to "do something" with. Even then it would need to read the value from the table to do a comparison.

What I'm trying to say is that if this was the case, it would actually make it less efficient to read the value, compare it to the one you're trying to update it to and then make a decision as to whether it should commit to the update operation. In your case it has to read Qty before it can work out what it needs to put in the A field, but even so, by the time it's compared values it might as well has completed the update and got on with the rest of its busy day :)

answered 2012-02-03T16:01:45.313

Your Answer