Performance of UPDATE commands on individual records

Ok, I'm at a crossroads in my program.
I've got a program that needs to throw an SQL update command to update
some individual records.

>From an efficiency standpoint, does SQL handle the UPDATE command
differently if the field is the same as the old field?
IE
Is it worth doing a string comparision on old vs new data in the
program, or should I just code to update all fields regardless and then
will the server optimize based on whether or not the data actually
changed?
Thanks,
Josh McFarlaneLogically SQL Server doesn't consider the diff at all.
If you want to only update small part of a large volume rows, doing string
comparision mostly yields better performance since that consumes less log
space.
For single line, I still suggest you "update when necessary", since some
triggers may sit there to enforce biz logic.
James
"Josh McFarlane" wrote:

> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
> Thanks,
> Josh McFarlane
>|||Josh McFarlane (darsant@.gmail.com) writes:
> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
Since it's a bit of work, I'm not sure that it's worth the effort, but
there are at least two scenarios where you can gain some performance.
One case is if you use merge replication. I'm not into replication myself,
but I got a question from a guy who is very good at replication, and he
wanted to reduce an update, so that only columns that were actually
changed were to be updated. Apparently, this made replication more
effective.
The other case concerns indexed columns. Consider this:
SELECT * INTO Orders FROM Northwind..Orders
create unique clustered on Orders
create index ix On Orders(CustomerID)
go
BEGIN TRANSACTION
UPDATE Orders
SET EmployeeID = 18
WHERE OrderID = 11000
At this point runs query from another window:
select count(*) from Orders WHERE CustomerID = 'RATTC'
RATTC is the customer id for order 11000. This query returns the
value 18 instantly, was not blocked. Now in the first window do this:
UPDATE Orders
SET EmployeeID = 118,
CustomerID = 'RATTC'
WHERE OrderID = 11000
and now try the SELECT COUNT(*) again. This time it will block.
If you are generatnig the UPDATE statement dynamically, and in client
code, then filtering on columns that have actually changed is probably
manageable. In a stored procedure it is just painful.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx