Originally posted by Scott Dunbar:
I have seen two methods, neither of which are free:
Just update everything in the row blindly. As you said for trigger based logging/auditing this can produce garbage. The DB overhead is not too bad but you're still passing alot of data.Maintain a list of "dirty" flags in the bean. The bean and whatever persists it to the database interact to ultimately say "is this field dirty?" and, if so, update it. This requires a bit more framework to implement.
The DB overhead of the first choice can vary quite a bit, depending on the DB implementation. If the table doesn't have any indexes or foreign keys, then the waste is limited to a little extra CPU time and a significantly bigger transaction log. If there are indexes or foreign keys, they will sometimes be checked/maintained even when the value isn't actually changing (depends on the DB implementation). For tables that are updated very very very frequently, the increased size of the transaction log can be very significant. For very low frequency updates, I wouldn't worry about the DB efficiency particularly. If you intend to program generically, I'd suggest the extra effort to write only the "dirty" columns; it'll pay off in the long run.