The logic of "no change" updates

jimmo · Worker Joined: Dec 08, 2005 Posts: 107

Hi All!

I am in the process of implementing a combination of the standard Nuke groups and the NSN groups. When I update the Nuke points, I want to immediately make sure that the user is the correct NSN group. Rather than querying the database to check if the user in the right group, I simply do an update for that user even if there is no change.

I found a couple of other places where this type of thing is already being done. One is update_points() in mainfile.php. In this case, the update could be avoided by first checking if points=0, in which case no update is necessary.

However, it seems to me that in my case since I have to access the database anyway why not simply do an update and not a query and then update. In any case, I have to do at least the query every time I add points. However, I am saving the time for the second DB access when I actually have to make a change. Granted, the user will have to make dozens if not hundreds of changes to move from one group to another, so an update that is really necessary will not be a common occurrence.

Writing the code would be simpler (which is a happy thing) as I don't have to build in any checks. Also, I do not see any real problems at the moment with doing the update all of the time, but I am curious as to what others think about doing it.

Regards,

jimmo

montego · Posted: Thu Aug 17, 2006 5:44 am

In this specific case that you mention, the approach sounds reasonable.

Just an "aside": I do not have experience with mySQL from a DBA perspective, but one of the things I would be concerned with if this was Oracle, is if I had archive logging turned on (i.e., all edits are logged for forward recovery purposes). An update in this case, would be very wasteful and unnecessary, and I would opt for the query/check option.

jimmo · Worker Joined: Dec 08, 2005 Posts: 107

I've been googling for an atomic way to check and update, but I haven't found anything. A SELECT selects and an UPDATE updates. Hmmmm....

What I though about was to update using a WHERE that matches the user ID and not the group ID. I know what the group should be and only do an update if the group is different. While that does not prevent the second access, it prevents the update when it is not necessary.

Since there are only a handful of groups, I though about hardcoding the values into my custom include file which is loaded anyway. But the question is whether the extra code is worth any potential speed gains.

montego · Posted: Fri Aug 18, 2006 6:34 am

Quote:

What I though about was to update using a WHERE that matches the user ID and not the group ID. I know what the group should be and only do an update if the group is different. While that does not prevent the second access, it prevents the update when it is not necessary.

Sounds reasonable.

Quote:

But the question is whether the extra code is worth any potential speed gains.

Depends upon what you are after. If you see no issues with extra db calls, then add them. If you are seeking out the absolute most efficient way to do it, then it begs the question.

You should be fine with either approach.