Is it better to update or increment a value when persisting to a database?
.net, sql, sql-server
Solution
There is a difference in the two operations. The first says:
The user should have two points; no more, no less.
The second says:
The user should get two more points, in addition to what he/she already has.
I would refrain from putting this kind of data logic in the business logic layer. The business logic should be "the user gets two points" and it should tell the database this. It shouldn't take matters into its own hands and say "well the database told me the user has two points, so now they have four!" This is dangerous if there's latency between the business layer and the database, or very many updates going on simultaneously in multiple threads.
I realized I didn't actually put which choice I prefer in clear text: Determine in the business logic how many points a user should get. Then issue a statement that tells the database to increment the score of the user by those points. By doing this, you're making the database responsible for keeping the data consistent, which is a task that should only ever belong to a database. I mean it's what they do, right?
UPDATE users SET points = points + ? WHERE user_id = ?;
Your business logic layer would simply fill in the blanks.
If you're doing a huge project, you might even want to consider putting this in a stored procedure since you might change the data structure in the future (such as breaking out the points to another table or some such):
userChangePoints ?, ?
Problem
I have an application where a user performs an action and receives points. Would it be a better idea to perform the arithmetic in the application and update the points database field with the resulting value, or have the database do the math? Assuming a user with 0 points is to receive 2 additional: ``` //app does the math (0+2) and issues this statement update users set points = 2 where id = 1 ``` vs ``` //app only knows to update by 2, db does the math update users set points = points+2 where id = 1 ``` Is there any difference in terms of SQL performance? Is one approach better than the other as far as application design, where this logic should reside, etc? Edit: This edit may be coming too late to serve much good, but I just wanted to respond to some of the feedback and make a clarification. While I'm curious of any db performance difference, this is not a concern. My concern is where this logic would best reside and why one should be favored over the other and in which scenarios. On one hand nearly all of my logic resides within the application so it would be consistent to do the math there, like Hank's answer. But on the other hand, there is some potential latency/threading issues that may suggest the logic should be performed by the db, as brought up by Blixt and Andrew.