| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2010-11-10 : 16:04:20
|
GreetingsFront end calls an update sproc but user might have changed only 2 out of 5 fields.This is what I am planning right nowCREATE PROCEDURE [dbo].[uptable] (@id INT, @parm1 int = NULL, @parm2 int = NULL )UPDATE t SET COALESCE(@parm1 ,goobleID), FROM table t WHERE id = @id AND ( fiel1dID <> @parm1 OR fiel2dID <> @parm2) ThanksHow do I update only changed fields and leave the rest alone?Thanks!If you don't have the passion to help people, you have no passion |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-10 : 16:12:56
|
| You could loop through all the columns, compare the value(s) passed in to the appropriate column, and if they are different, run some update code.This could also give you some additional control for auditing, if you need it. |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-11-10 : 16:14:31
|
| thanks Tim. I was hoping to avoid loops and do it set based. I have the auditing happening via output inserted.x dumping into a table variable which I use later to populate my audit tableIf you don't have the passion to help people, you have no passion |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-10 : 16:24:03
|
| Yeah, I was afraid that might not be a viable option...I think the hard part is all the potential variability. But I may not be fully understanding where you're going with it either.I assume that you have an update sproc that has all the parameters set up, and you would just set each field = the appropriate parameter. And you want to look at each parameter passed in, see if it's different than the value currently in the field, and if it is update it.Of course, you could get real crazy, and have two parameters, one that holds a list of values, and one that holds the column names, then use some funky dynamic sql to update that way.The last thing I can think of right now would require SS2008. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-11-10 : 16:40:55
|
| yes Merge command in sql 2008 would take care of this. actually you have give me an idea, save the current record into a table variable do comparison to incoming parms. let me try thatIf you don't have the passion to help people, you have no passion |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-10 : 16:45:24
|
| Let us know how it works out, because I'd be real curious to see it. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-11-10 : 17:31:40
|
One wacky idea. which I've actually implemented before and a co-worker nicknamed "wildly optimistic concurrency"  , is this:The frontend application kept track of what fields changed and passed a delimited list of those fields to the update SP. Then the SP did something like this:SP parameters:@tableAID = 12@col1 = 'val1'@col2 = 'val2'@ChangedColumns = '!!col1!!col2!!' update a set a.col1 = case when charindex('!!col1!!', @changedColumns) > 0 then @col1 else col1 end ,a.col2 = case when charindex('!!col2!!', @changedColumns) > 0 then @col2 else col2 endfrom tableA awhere tableAID = @tableAIDThe objective is that we wanted last user in to win for concurrent updates but if the concurrent users updated different entry fields we didn't want the first guy' changes in to be overwritten by the second guy's unchanged values.Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-10 : 19:19:15
|
| What exactly is the issue you are trying to solve? Are you saying the front-end only passes values for the, say, the 2 parameters that have changed and not all 5? Or does it pass all 5, but you are trying to make it more efficient? What if the value is updated from some more tangible values to a NULL, then the COALESCE will mess things up.The database still has to read in, modify and save the data page anyway. So, I doubt not updating a field to the same value is going to help much from a performance perspective.EDIT: I missed the part about Auditing.. How are you auditing? Is it totally custom or are you using triggers or CDC? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-11-11 : 10:00:37
|
| the issue I am trying to solve is to only update the fields that have changed. it does pass all 5 but trying to make it more efficient. no value will be changed to NULL because they are all required fields. maybe I am going overboard on this.I am doing custom auditing, using custom audit tables, no triggers and no CDCappreciate the feedbakcIf you don't have the passion to help people, you have no passion |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-11 : 10:06:39
|
| I think a better option is to do the deciding in the front end. Let the class(es) decide what to update and how, and maybe you can set up some sort of generic process in SQL to handle the details provided. Much as I dislike having the SQL formed in the front end, I think this is one case where it is helpful. Do your proper checking of the values passed in, and you should be OK. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-11-11 : 10:14:16
|
| If your concern is just about performance of updating 1 or 2 values in a row vs. all values then I wouldn't bother. I don't think there is anyway to achieve that without individual update statements for each column which obviously defeats the purpose. (Unless you take TimSman's suggestion of application side sql)My suggestion earlier was not about performance it was about concurrency and the least possible impact from simultaneous updates.EDIT:I also don't think you would see any noticeable performance gain of updating 1 column vs all columns when you update 1 row per SP call. The time required to find the row and obtain a lock is where the time will be spent. Once that is done updating 1 vs. all columns won't matter. IMOBe One with the OptimizerTG |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-11 : 11:03:20
|
quote:
Originally posted by yosiasz
the issue I am trying to solve is to only update the fields that have changed. it does pass all 5 but trying to make it more efficient. no value will be changed to NULL because they are all required fields. maybe I am going overboard on this.I am doing custom auditing, using custom audit tables, no triggers and no CDCappreciate the feedbakcIf you don't have the passion to help people, you have no passion
Oh well yeah, in that case you really aen't going to gain anything because SQL still has to read, update and save the page the data is on. Thus, you haven't saved anything, or at least enough to make a difference. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-11-11 : 14:59:36
|
| got it! history table implemented with what why how when etc. I don't care I just have OCDIf you don't have the passion to help people, you have no passion |
|
|
|
|
UPDATE only changed fields