Are you just updating changed fields or all fields?

Question

I am wondering if it is worth the server time when updating a record to retrieve an existing record, scroll through the fields that check for changes, and only put the changed fields in the update request? (I use MySQL and PHP.)

The main reason for this is to reduce the size of the update request for the change log. Typically, a query can contain 15 fields, but only 2 fields actually change. This query can also be used for logging, because it will only contain modified fields and therefore easier to parse.

I am worried about the time it takes to retrieve an existing record.

Or is there a way to extract from MySQL which fields he updated?

Answer 1

I think this is worth changing, but you probably shouldn't make a choice before inserting.

I am only updating the fields that have been changed, this is part of the operation of my DbEntity class that follows the activerecord pattern. It costs a little more to do this because I keep the current recording and the original recordings - just copying when loading the recording.

Reasons - brevity - not very high performance. You can also check concurrent modification by adding a where clause to the old value of the updated fields and throwing the corresponding error.

In the write / update method:

$s1 = ""; foreach ($this->record as $key => $value) { // only update fields that have been changed if ($value != $this->orig_record[$key]) { $s1 .= $comma."`$key`='".mysql_real_escape_string($value)."'"; $comma = ", "; } } $query = "UPDATE ".$this->table." SET $s1 where {$this->id_field}='".$this->get_keyfield()."'"; $query .= $this->extra_sql_update; mysql_query($query); $ar = mysql_affected_rows(); // // the number of affected rows is actually those changed by the update operation, which will // either be zero, or 1. If the query affects more than one row then we have a problem. if ($ar < 0 || $ar > 1) { cbf_error("cbf_dbentity: {$this->table} :: only one row (not $ar) must be affected by an insert operation. $query", E_USER_ERROR); } else { $new_id = $this->get_keyfield(); GlobalEventBus::notify_all(new AuditLogSQL($this->table, "update", $query)); } $this->orig_record = Array(); foreach ($this->record as $key => $value) $this->orig_record[$key] = $value; // // sanity check - ensure that what we have just written is actually there. $this->load($new_id); foreach ($this->orig_record as $key => $value) if (trim($this->record[$key]) != trim($value) && (!$this->record[$key] == "0" && $value="")) cbf_error("cbf_dbentity: {$this->table} :: record differs during write after reload: field $key was \"$value\", after write it is now \"". $this->record[$key]."\"",E_USER_ERROR); 

In boot mode

 $this->orig_record = Array(); foreach ($this->record as $key => $value) $this->orig_record[$key] = $value; 

Answer 2

At the most basic level, if I read your question correctly, you don’t want to blindly update the entire record if another user has already updated parts of this record that you have not actually changed. You blindly and uselessly return your updates.

I believe that your current algorithm may lead to a dirty write if you are going to read the current data for the update, allow the update in memory, and then read the record again so that you can find out which fields were updated. What happens if another user updates this record behind you, forcing your algorithm to believe that you are the one who updated this field? But first of all, you don’t have to read each entry twice to complete one update.

If your data often leads to conflicts, it may be useful for you to read about optimistic locking, even if you do not decide to implement it.

We have implemented one method here, in which you add the update timestamp or the incremental number of the updated number to your table. Then, in your sandbox / memory, you can track which fields you changed (oldvalue / newvalue), and you can freely release the SQL update for this record for these fields: "UPDATE ... WHERE UPDATENUM = the-original-number" (or WHERE UPDATETS = the-original-timestamp), making sure your SQL update also increments UPDATENUM or UPDATETS if necessary. If the records affected by this SQL update are 0, you know that someone else has already changed this record in the background, and now you have a conflict. But at least you did not overwrite any changes, and you can re-read the new data or resolve the conflict to your user.

Answer 3

The slowest point in your application will always be your access to the database, so if you can speed it up, this is a good idea. However, it really depends on how large your database and records are, and how large they are likely to grow, as to whether you should try to programmatically check if the items have been updated. If your database is small and access is already pretty fast, this might not be worth the time. But if speed can be improved, and it will give you additional benefits for your logging, then follow it.