Does it save time to check for changes before updating 2

[bryant89]

I was going to write a stored procedure that gets passed 5 values. These values may or may not have changed from what they currently are in the tables. What I was wondering is... Should I compare the values being passed to the procedure to the values stored in the tables or should I just update the row in the table no matter what. Or is there even a way to update the row in tsql?

Thanks in Advance.

 

[tlbroadbent]

How about a query that checks if you need to update any columns and handles everything in one statement?

Update TblName Set
colA=@varA,
colB=@varB,
colC=@varC,
colD=@varD,
colE=@varE
Where colA<>@varA
Or colB<>@varB
Or colC<>@varC
Or colD<>@varD
Or colE<>@varE

This updates all columns even if only one needs to be updated but won't update any if none need to be updated. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[bryant89]

yes that is exactly it. Thank you very much. I appreciate it. Once I saw your answer I was like duh....

Thanks Terry.

 

[fluteplr]

To answer the question generically, yes it is usually faster to check to see if the update needs to be made 1st.

Since on most systems read times are significantly faster than write times.

This is especially true if an update would cause a trigger to fire.

Obviously your mileage may vary if your are updating millions of records and <1% actually don't need the update checking will probably take more time.

 

[bryant89]

thanks fluteplr. It makes sense that it would save time. I just wasnt sure how much time or if it would matter. I am still in sql tsql &quot;training&quot;. I need to take some more courses on this stuff but they are far and few in the area I live in.

Thanks

Bryant