Running a query multiple times vs using a cookie

Question

A question about query/database performance:

I have a table that records how many “points” a user has earned. There is 1 row for every 1 point. When someone gains 20 points, 20 rows are inserted into the database. When someone looses 20 points, I will add a row with the value of “-1” in the database. Thus the number of points someone has can be calculated by performing a simple SELECT query that sums the points together.

Potentially, each individual user could have thousands rows to indicate points gained/lost, and my database could have thousands of users.

I need to show the points (for the one user who is logged in) on “every” page of the website.

The question is: will it hurt database performance to perform this SELECT query upon every page load, or is it more efficient to run the query once, store the value in a Cookie, and just display the Cookie on the page? The only risk I foresee is that if the user’s browser does not support Cookies, and that the Cookie is always updated to show the correct value.

Answer 1

A single query per page load isn't going to do much, but yes a cookie is more efficient, although not secure. You should use sessions so the user cant alter the data.

Answer 2

This is a quite cheap operation. If you have many thousands of users a clustered index on (userid, point) you can speed up the lookup.

While it is a quite cheap operation it still takes some time to execute an extra query. If you expect very high traffic volumes an alternative can be to store the current point in the user table in a column next to user name, etc. I suppose you fetch the user name for each page, then you can get the current point almost for free.

The point table can be kept for reference and detailed information about why and when points were earned.