Is it better to update or increment a value when persisting to a database?

Question

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.

Answer 1

I believe this to be the case of "premature optimization" or overoptimization.

The answer is: it doesn't matter. Do whatever makes your application more simple, clear and maintainable.

ADDED: As to performance, I do not believe you notice any difference unless you run this update on all data in the table. But if you must know, I believe the second option will take slightly more tacts since you first need to read the value, then calculate on the basis of it and then write it back. So you spare one read operation with your first option.

Answer 2

I would say the same as New in town, don't fret about it too much yet.

I would write what ever makes sense and easier to read first (so the first one), profile both ways then make the change if needed.

Answer 3

Put the addition wherever the rest of the business logic is being performed. If your app lets the DB handle all the business logic, do the math in the SQL. If your business logic is in the application layer, put the math there and issue a plain UPDATE.

Answer 4

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 ?, ?

Answer 5

Whilst in some sense it can be viewed as premature optimisation, for a multi-threaded / user environment difference between the two would be quite critical in that directly reading and then setting the value allows two transactions to interfere and result in one of them potentially being lost.

Sending in a points update with points=points+2at least isolates the two statements, but then if submitted twice by different users simultaneously would give you a results of 4.

That equally may not suit the situation / application, but I think your choice should take into account what isolation / protection you need.

This is then specfic to the app, can a user be logged in twice etc, but the principal of the difference between the two remains.